Distinct Count If's

[clarkey1988]

Hi,

I have a calculated column as below. It's calculating what I need. However, I don't want week number or year to be manually typed in. I need it to take the value in that row for both the week number and year columns.

Driver 2 Prefix Single DC Runs = CALCULATE(DISTINCTCOUNT('Trip Detail'[ord_hdrnumber]),FILTER('Trip Detail','Trip Detail'[True Driver Type]=1 && 'Trip Detail'[Week Number]=4 && 'Trip Detail'[Year]=2021 && 'Trip Detail'[LegHeaderDriver1]=EARLIER('Trip Detail'[Driver 2 Prefix])))

Any help wil be greatly appreciated.

[Ashish_Mathur · Accepted solution]

Hi,

Does this modified verion work

=Driver 2 Prefix Single DC Runs = CALCULATE(DISTINCTCOUNT('Trip Detail'[ord_hdrnumber]),FILTER('Trip Detail','Trip Detail'[True Driver Type]=1 && 'Trip Detail'[Week Number]=earlier('Trip Detail'[Week Number]) && 'Trip Detail'[Year]=earlier('Trip Detail'[Year]) && 'Trip Detail'[LegHeaderDriver1]=EARLIER('Trip Detail'[Driver 2 Prefix])))

Hope this helps.

[clarkey1988]

Yes thank you!!!

[Ashish_Mathur]

You are welcome.