topic Counting between 2 date columns, need formula to now return the Distinct Count in DAX Commands and Tips

Counting between 2 date columns, need formula to now return the Distinct Count

6Rastaman9 — Wed, 21 Dec 2022 00:49:46 GMT

Hi there, I had been using the working formula below until the records increased and I now have multiple rows returning for the same case. I need to calculate the Distinct Cases. Any help would be really appreciated. Thanks!

Case # RoleStartDate RoleEndDate ReturnCount

1000 2020 2021 1 in 2020 and 1 in 2021

1000 2020 2021 not counted, not distinct

My current formula:

# of Cases (RoleDate) =

SUMX (

CROSSJOIN ( VALUES ( 'Calendar'[Date] ), VALUES ( MainData[RoleStartDate] ) ),

CALCULATE (

VAR SelectedDates =

VALUES ( 'Calendar'[Date] )

RETURN

SUMX (

'MainData',

VAR ActiveDates =

CALENDAR ( MainData[RoleStartDate], COALESCE ( MainData[RoleEndDate], TODAY ( ) ) )

RETURN

IF ( COUNTROWS ( INTERSECT ( SelectedDates, ActiveDates ) ) > 0, 1, 0)

)

Re: Counting between 2 date columns, need formula to now return the Distinct Count

amitchandak — Wed, 21 Dec 2022 02:24:47 GMT

6Rastaman9 , You can have a separate year table and use the way given below

Between Dates - Dates between
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

Re: Counting between 2 date columns, need formula to now return the Distinct Count

6Rastaman9 — Wed, 21 Dec 2022 20:47:19 GMT

Thanks, very informative but not sure its what I need in this senerio. I may not be expressing it accurately.

I used your formula below which worked! but bc I now have mutiple rows of the same case # in my fact table, this formula is counting each row, therefore the same case # is being counted multiple times.

In my table I need to count distinct cases equalling 2 total cases in 2020 (start date) and those same 2 total cases in 2021 (end in 2021 so still counted as cases). I do have a Date table as well.

Here is what I used, how do I now calculate the distinct cases? ANY help would so be appreciated.

# of Cases (RoleDate) =

SUMX (

CROSSJOIN ( VALUES ( 'Calendar'[Date] ), VALUES ( MainData[RoleStartDate] ) ),

CALCULATE (

VAR SelectedDates =

VALUES ( 'Calendar'[Date] )

RETURN

SUMX (

'MainData',

VAR ActiveDates =

CALENDAR ( MainData[RoleStartDate], COALESCE ( MainData[RoleEndDate], TODAY ( ) ) )

RETURN

IF ( COUNTROWS ( INTERSECT ( SelectedDates, ActiveDates ) ) > 0, 1, 0)

)

Re: Counting between 2 date columns, need formula to now return the Distinct Count

Anonymous — Thu, 22 Dec 2022 11:10:47 GMT

Hi 6Rastaman9 ,

Please update the formula of measure [# of Cases (RoleDate)] as below and check if you can get the correct result...

# of Cases (RoleDate) = VAR _seldate = SELECTEDVALUE ( 'Calendar'[Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( MainData[CaseNumber] ), FILTER ( MainData, MainData[RoleStartDate] <= _seldate && COALESCE ( MainData[RoleEndDate], TODAY () ) >= _seldate ) )

If the above one can't help you get the desired result, please provide some sample data in your table 'MainData' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Re: Counting between 2 date columns, need formula to now return the Distinct Count

6Rastaman9 — Thu, 22 Dec 2022 21:28:34 GMT

Anonymous it works! Thank you so so much :).

2 follow up Qs if I may:

1 - I noticed that the # of cases doesnt total:

2 - Is it because I do not have a date hierarchy that I cant get the totals for just January or just the year? Choosing 2022 shows a Blank in a card visual.

Re: Counting between 2 date columns, need formula to now return the Distinct Count

Anonymous — Mon, 26 Dec 2022 11:55:02 GMT

Hi 6Rastaman9 ，

You can create a measure as below and put this measure on the card visual to replace the original measure [# of Cases (RoleDate)] and check if it can return the expected result...

Measure = SUMX ( GROUPBY ( 'Calendar', 'Calendar'[Year], 'Calendar'[Month], 'Calendar'[Date] ), [# of Cases (RoleDate)] )

Best Regards