Sum of the result of this measure per day in the period

[grggmrtn]

Yup, this one is complicated...

I have a measure that calculates the number of persons (CPR) that have an active service within a period. "Active service" is defined by a start and stop date, compared to the StartDate (StartDato) and StopDate(SlutDato) within the filter. Let's see if my code makes more sense than my explanation 🙂

Antal Borgere = 
VAR StartDato =
    MIN ( Dato[Dato] )
VAR SlutDato =
    MAX ( Dato[Dato] )
RETURN
CALCULATE(
    DISTINCTCOUNT('Borger'[CPR]),
        FILTER(
            'Faelles_Aktivitet', 
            'Faelles_Aktivitet'[StartDato] <= SlutDato && 
            'Faelles_Aktivitet'[StopDato] >= StartDato 
            )
    )

 This DAX works perfectly. BUT I need a variation of it.
Let's say that I've set the date filter to 2020. I get one number, which tells me how many distinct persons there were that had an active service in 2020. What I NEED is to figure out the sum of distinct persons for EACH DAY in that period. SO for example
If the filter is set to 2020, then I need to calculate
[Antal borgere] for 1.01.2020

[Antal borgere] for 2.01.2020

[Antal borgere] for 3.01.2020

[Antal borgere] for 4.01.2020
and return the sum of all of these.

Just as a reference - for 2020 I have about 240 distinct persons, and the result I need should be a bit over 80,000

Can anyone help?

[Anonymous · Accepted solution]

That's absurdly easy in DAX 🙂

[Your measure] =
SUMX(
    DISTINCT( Dato[Dato] ),
    [Antal Borgere]
)

By the way, your measure could have been written like this for performance reasons:

Antal Borgere =
VAR StartDato = MIN( Dato[Dato] )
VAR SlutDato = MAX( Dato[Dato] )
VAR Result =
    CALCULATE(
        DISTINCTCOUNT( 'Borger'[CPR] ),
        KEEPFILTERS( 
            'Faelles_Aktivitet'[StartDato] <= SlutDato
        ),
        KEEPFILTERS(
            'Faelles_Aktivitet'[StopDato] >= StartDato
        )
    )
RETURN
    Result

[grggmrtn]

The SUMX worked perfectly - thanks

Your KEEPFILTERS version of my measure actually didn't - like REALLY didn't work. I went from a total of 9600 to 56000

[Jihwan_Kim]

Hi, @grggmrtn 

Please correct me if I wrongly understood your question.

I tried to create a similar sample as what you described above.

Please check the below pictures and the link down below whether it is what you are looking for.

active Person Count =

CALCULATE (

COUNTROWS ( FactTable ),

FILTER (

FactTable,

FactTable[Startdate] <= MAX ( Dates[Date] )

&& FactTable[Enddate] >= MIN ( Dates[Date] )

)

)

https://www.dropbox.com/s/bth2upwxhgyjacr/grggmrtn.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[grggmrtn]

More or less. Difference being that my date table is detached (because the DAX is doing the work) and PersonId is in it's own dimension (star schema)

Also, you use COUNTROWS where I use DISTINCTCOUNT.