Metabase - Create a query to show annual compliance unit availability for every organization

[Grulin]

Describe the task Create a query in Metabase to display an organization's annual compliance unit availability in TFRS/LCFS portal. Include filters for selecting the organization and the year.

Purpose To provide a clear and concise view of an organization's compliance unit availability on an annual basis, so that analysts can ensure that the following were correct:

• Total compliance units available at the start of the year
• Compliance units earned during the year
• Compliance units used during the year
• Compliance units remaining at the end of the year

Acceptance Criteria

• [ ] Given I am an IDIR user, When I access Metabase, then I can run a query to see the annual compliance unit availability for a selected organization.

• [ ] Given I am an IDIR user, When I run the query, then I see the correct annual compliance unit data for the chosen organization.

Additional context

• Ensure the query pulls data from the correct tables and fields in the database.
• Must factor in that available compliance units will reflect those earned up to March 31st within the following compliance year (For example. units earned from January 2022 to March 31st, 2023 should be available for use for the 2022 reporting period).

[AlexZorkin]

The query works fast which is awesome. The filtering is also super useful.

@Grulin Take a look at this query when you have a chance and let us know what you think.

It's in Metabase under the "Annual compliance unit availability for every organization" query.

[Grulin]

Hi Alex and Prashanth,

Brilliant! This is going to be so useful for us.

I compared this to the calculation we did for one of the major suppliers and noticed two things: 1) The query is not accounting for reduction transactions. This can be included in the "units used" column and we'll have to just document that this includes "transfers" and "reductions". 2) It is only accounting for transactions within a calendar year. For example, 2015 is only including transfers done up until December 31st, 2015 when the "2015 period" should also include transfer done between January through to March 31st, 2016 as these units can also be applied to the 2015 period and beyond. Hopefully this is an easy date range adjustment.

Such a great start to this query. I am so appreciative that this information can be so easily available. Thank you!

[prv-proton]

Hi Alex and Prashanth,

Brilliant! This is going to be so useful for us.

I compared this to the calculation we did for one of the major suppliers and noticed two things:

1. The query is not accounting for reduction transactions. This can be included in the "units used" column and we'll have to just document that this includes "transfers" and "reductions".
2. It is only accounting for transactions within a calendar year. For example, 2015 is only including transfers done up until December 31st, 2015 when the "2015 period" should also include transfer done between January through to March 31st, 2016 as these units can also be applied to the 2015 period and beyond. Hopefully this is an easy date range adjustment.

Such a great start to this query. I am so appreciative that this information can be so easily available. Thank you!

Hi @Grulin,

I have made the necessary changes as per the comment, kindly review the same model. Thanks.