I need to count the number of unique year-to-date claims using a DAX formula. I am using slicers and report filters.
The built-in time intelligence measures don't seem to handle this.
I'm new to DAX and struggling to figure this out.
My data is basically structured like this:
* GL transactions, consisting of many rows for each "Claim No" and each financial transaction, with a key to the Fiscal Period (Date) Dimension and a key to the GL Account dimension
* Fiscal Period Dimension has a built-in Power BI date hierarchy based on Fiscal Period End Date. Fiscal Period Dimension also has Fiscal Month and Fiscal Year as separate variables that are not part of the built-in Power BI date hiearchy.
In plain business language, the formula should work something like this:
* Count distinct claims with an expense in the fiscal year up to the end of the fiscal month.
(I'm using filters and slicers to control the GL accounts, fiscal periods, and financial transactions to include in the report.)
In sql, my formula might look like this (assuming I only want 2019 and 2020 totals.).
select q1.*, q2.*
( select count ( distinct ( claim_no ) ) as YTD_2019_cnt from gl_transactions where fiscal_year = 2019 and fiscal_month <= 8 and gl_account in ('FOO1', 'FOO2', 'FOO3') and gl_transaction not in ('FOO4')
) q1 ,
( select count ( distinct ( claim_no ) ) as YTD_2020_cnt from gl_transactions where fiscal_year = 2020 and fiscal_month <= 8 and gl_account in ('FOO1', 'FOO2', 'FOO3') and gl_transaction not in ('FOO4')
Any suggestions how to do a unique count of ytd claims using DAX?
Good choice not to rely on the built-in date/time intelligence. It consumes a lot of resources for little to no gain. Better use a separate calendar table that also allows you to specify your fiscal boundaries easier.
Have a look at this thread for a "Previous year to date" discussion.