Calculation of differences of values between 2 dates

[DanielKay]

Hello,

I'd like to calculate the difference between the amount of entries between two dates.

I created this measure to see the differences in stocks between two or more teams:

Performance = 
VAR __maxDate = CALCULATE(Maxx(data,data[Date]))
VAR __minDate = CALCULATE(Minx(data,data[Date]))
RETURN
CALCULATE(COUNT(data[Identifier]), FILTER(data,data[Date]=__maxDate)) -
CALCULATE(COUNT(data[Identifier]), FILTER(data,data[Date]=__minDate))

All in all, this measure works like a charm, but the numbers don't add up, when one team hasn't got any entries in __minDate or in __maxDate.

For example following dataset:

Identifier	Team	Date
ABC-1	Team A	12.12.2024
ABC-2	Team A	12.12.2024
ABC-3	Team A	12.12.2024
ABC-4	Team B	12.12.2024
ABC-5	Team A	12.12.2024
ABC-1	Team A	19.12.2024
ABC-2	Team A	19.12.2024
ABC-3	Team A	19.12.2024
ABC-4	Team B	19.12.2024
ABC-5	Team B	19.12.2024

I get the expected Performance: Team B +1, Team A -1

Let's edit the database, so Team B only exists in the second date.

Identifier	Team	Date
ABC-1	Team A	12.12.2024
ABC-2	Team A	12.12.2024
ABC-3	Team A	12.12.2024
ABC-4	Team A	12.12.2024
ABC-5	Team A	12.12.2024
ABC-1	Team A	19.12.2024
ABC-2	Team A	19.12.2024
ABC-3	Team A	19.12.2024
ABC-4	Team B	19.12.2024
ABC-5	Team B	19.12.2024

My expected Performance should be: Team B +2, Team A -2

Somehow my DAX measure can't calculate the numbers correctly. There are no filters applied.

Someone got any solution to that problem? Any workarounds? Maybe using a different measure?

Thanks in advance.

Kind regards,

Daniel

[danextian · Accepted solution]

Hi @DanielKay 

As there is only one date, both min and max dates are the same and so are values 1 and 2. You can see in the screenshot below that Val1 and Val2 for team B are both 2 so 2-2 = 0. You can add a conditional check in your formula and return only Val1 1 if the condition is true.

Performance3 = 
VAR __maxDate =
    CALCULATE ( MAXX ( data, data[Date] ) )
VAR __minDate =
    CALCULATE ( MINX ( data, data[Date] ) )
VAR __DateTest = __maxDate = __minDate
VAR __Val1 =
    CALCULATE (
        COUNT ( data[Identifier] ),
        FILTER ( data, data[Date] = __maxDate )
    )
VAR __VAl2 =
    CALCULATE (
        COUNT ( data[Identifier] ),
        FILTER ( data, data[Date] = __minDate )
    )
RETURN
    IF ( __DateTest, __Val1, __Val1 - __VAl2 )

[bhanu_gautam]

@DanielKay , Try using 

DAX
Performance =
VAR __maxDate = CALCULATE(MAX(data[Date]))
VAR __minDate = CALCULATE(MIN(data[Date]))
VAR __maxDateCount = CALCULATE(COUNT(data[Identifier]), FILTER(data, data[Date] = __maxDate))
VAR __minDateCount = CALCULATE(COUNT(data[Identifier]), FILTER(data, data[Date] = __minDate))
RETURN
IF(ISBLANK(__maxDateCount), 0, __maxDateCount) - IF(ISBLANK(__minDateCount), 0, __minDateCount)

[DanielKay]

Sadly, this doesn't work. Same result again. Team A -2; Team B 0.