cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Dax year-to-date ytd unique count

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.*

from

( 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')

) q2

 

Any suggestions how to do a unique count of ytd claims using DAX?

6 REPLIES 6
Super User II
Super User II

This is definitely not how your SQL would look like (it would result in a cartesian product) - I think you wanted to say union all.

 

In DAX you can use OR() or the equivalent ||  to combine the results.

 

Meta code:

 

CALCULATE(DISTINCTCOUNT([Claim No]),{2019 filters} || {2020 filters})

 

Or, if there is risk of overlap,  calculate the values separately and then let the visual add them up.

 

Thanks for your reply, but you are wrong about what the sql returning a cartesian product. Here is actual output of my sql using real vars and parms:

 

YTD_2019_CNTYTD_2020_CNT
15041274

 

Keep in mind the sample sql was solely to illlustrate one example of how the resulting output might look. The important concepts are these:

* each claim can have multiple financial transactions in a given fiscal period (e.g. fiscal year-fiscal month).

* I want a year-to-date unique count of claims up to the end of the chosen fiscal year-fiscal month.

* report/page filters will be applied in the report to subset as needed. The calculation (unique ytd count of claims) should adjust automatically to the user-selected filters.

 

 

Regarding your comment about using filters in the DAX equation . . . 

 

The primary filter will be fiscal year/fiscal month or fiscal period end date (but NOT the built-in Power BI date hieararchy????)???

 

For example, this works with the built-in time intelligence measures:

GL Amount YTD =

IF(
    ISFILTERED('Fiscal Period Dimension'[Fiscal Period End Date]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    TOTALYTD(
        [GL Amount],
        'Fiscal Period Dimension'[Fiscal Period End Date].[Date]
    )
)
 
 
However, the distinctcount function does not appear to be valid in this formula.
 
So, I expect I have to use filters (as you've pointed out). I just don't know how to structure them properly.

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.

https://community.powerbi.com/t5/Desktop/Cumulative-Previous-Year-over-Year-Calculation-With-custom-...

 

 

Community Support
Community Support

Hi @Mike_Yukon ,

 

You may create measure like DAX below.

 

CountClaim_YTD =
CALCULATE (
    DISTINCTCOUNT ( 'gl_transactions'[claim_no] ),
    FILTER (
        'gl_transactions',
        'gl_transactions'[fiscal_year] = MAX ( 'gl_transactions'[ fiscal_year] )
            && 'gl_transactions'[fiscal_month] <= 8
            && 'gl_transactions'[gl_account] IN { 'FOO1', 'FOO2', 'FOO3' }
            && 'gl_transactions'[gl_transaction] <> 'FOO4'
    )
)

 

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for the reply. I'm now up to my eyeballs in other project work and will check out your response when I have some relief.

Hi  @Mike_Yukon  ,

 

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

 

Best regards

Amy

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors