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

[6Rastaman9]

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)

            )

    )

)

[6Rastaman9]

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)

            )

    )

)

[v-yiruan-msft · Accepted solution]

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

[6Rastaman9]

@v-yiruan-msft 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.

[v-yiruan-msft]

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

[amitchandak]

@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-o...