Get differerence of sums between all dates in table

[JankoC]

Hello!

So I have this table (raw and image of only the first two rows below) from which I created a new table which contains sum of "SteviloSporocil" column for each "Zdravstveni dom" grouped by date "Datum".

Zdravstveni dom

Uporabnik

Delovisce

DatumRegistracije

CertRegistracija

SteviloPrijav

SteviloSporocil

TekstovnaVkljucena

StanjeRegistracije

Bolniska

Narocilnica

MTP

Recepti

Datum

Dom A

3000000000000443227

6621

Tuesday, 5 April 2022

ne

1

FALSE

Registriran

0

0

0

0

Sunday, 12 June 2022

Dom B

3000000000000251317

6621

Wednesday, 20 April 2022

ne

1

FALSE

Registriran

0

0

0

0

Sunday, 12 June 2022

The result is seen in the picture and table below. I get the sum of "SteviloSporocil" for each unique date for each "Zdravstveni dom". The sum for each next date is based from the previous date (for example "Dom E" on 11th of June 2022 has the sum of 260, on the 12th of June 2022 the sum is 2 more which means it becomes 262, and on the 13th of June 2022 it becomes 263 which is 1 more than the previous date. So essentialy what I need is to subtract each sum from sum of the previous dates (instead of "Vsota sporočil" for "Dom E" by dates being 260, 262 and 263, I'd like it to be 260, 2, 1 (growth from previous date). 

Datum	Vsota sporočil	Zdravstveni dom
11-Jun-22	220	Dom A
11-Jun-22	9	Dom B
11-Jun-22	10564	Dom C
11-Jun-22	93	Dom D
11-Jun-22	260	Dom E
11-Jun-22	5001	Dom F
13-Jun-22	230	Dom A
13-Jun-22	9	Dom B
13-Jun-22	10564	Dom C
13-Jun-22	96	Dom D
13-Jun-22	263	Dom E
13-Jun-22	5079	Dom F
12-Jun-22	220	Dom A
12-Jun-22	9	Dom B
12-Jun-22	10564	Dom C
12-Jun-22	93	Dom D
12-Jun-22	262	Dom E
12-Jun-22	5010	Dom F

What I've tried so far is this - get sum of the max date (which in this case is June 13th 2022) and subtract the sum of the (max date - 1) from it but what I get is just the total sum (I'm guessing because it will always take the last date and the date previous from the last date, instead of iterating through all the dates) - so in this case the sum between dates is the same as the total sum but I don't know why.

Desired Result = 
(
    var test =
        CALCULATE(SUM('Datum tabela'[Vsota sporočil]), FILTER('Datum tabela', 'Datum tabela'[Datum]=MAX('Datum tabela'[Datum])))
         -
        CALCULATE(SUM('Datum tabela'[Vsota sporočil]), FILTER('Datum tabela', 'Datum tabela'[Datum]=MAX('Datum tabela'[Datum])-1))

    return test
)

 I managed to hardcode the dates to get the results that I want but this is far from optimal.

DatumSum1 = 
(
    var datumSum =
        CALCULATE(SUM('Datum tabela'[Vsota sporočil]), FILTER('Datum tabela', 'Datum tabela'[Datum]=MAX('Datum tabela'[Datum])-2))
         -
        CALCULATE(SUM('Datum tabela'[Vsota sporočil]), FILTER('Datum tabela', 'Datum tabela'[Datum]=MAX('Datum tabela'[Datum])-3))

    return datumSum
)

DatumSum2 = 
(
    var datumSum =
        CALCULATE(SUM('Datum tabela'[Vsota sporočil]), FILTER('Datum tabela', 'Datum tabela'[Datum]=MAX('Datum tabela'[Datum])-1))
         -
        CALCULATE(SUM('Datum tabela'[Vsota sporočil]), FILTER('Datum tabela', 'Datum tabela'[Datum]=MAX('Datum tabela'[Datum])-2))

    return datumSum
)

DatumSum3 = 
(
    var datumSum =
        CALCULATE(SUM('Datum tabela'[Vsota sporočil]), FILTER('Datum tabela', 'Datum tabela'[Datum]=MAX('Datum tabela'[Datum])))
         -
        CALCULATE(SUM('Datum tabela'[Vsota sporočil]), FILTER('Datum tabela', 'Datum tabela'[Datum]=MAX('Datum tabela'[Datum])-1))

    return datumSum
)

This is what I get if I don't include the date in the visualization. The values in the columns "DatumSum1", "DatumSum2" and "DatumSum3" are all correct but I'd like them to be in the same column, depending on the date.

And this is what I get if I do include the date in the visualization. As you can see the Desired Result is still wrong as there is not subtraction from the previous dates. Ideally the result would look like the below image but with the correct sums in the "Desired Result" column. I would appreciate any help and tips with this case.

[v-chenwuz-msft · Accepted solution]

Hi @JankoC ,

Maybe you can try this code:

Measure =
VAR _first =
    CALCULATE ( MIN ( 'Table'[Datum] ), ALLSELECTED ( 'Table'[Datum] ) )
VAR _diff =
    SUM ( 'Table'[Vsota sporočil] )
        - CALCULATE ( SUM ( 'Table'[Vsota sporočil] ), PREVIOUSDAY ( 'Table'[Datum] ) )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Datum] ) = _first,
        CALCULATE (
            SUM ( 'Table'[Vsota sporočil] ),
            FILTER ( ALLSELECTED ( 'Table'[Datum] ), [Datum] = _first )
        ),
        _diff
    )

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.