Forum Discussion

JankoC's avatar
JankoC
Regular Visitor
4 years ago
Solved

Get differerence of sums between all dates in table

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 domUporabnikDelovisceDatumRegistracijeCertRegistracijaSteviloPrijavSteviloSporocilTekstovnaVkljucenaStanjeRegistracijeBolniskaNarocilnicaMTPReceptiDatum
Dom A30000000000004432276621Tuesday, 5 April 2022ne1 FALSERegistriran0000Sunday, 12 June 2022
Dom B30000000000002513176621Wednesday, 20 April 2022ne1 FALSERegistriran0000Sunday, 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). 

 

DatumVsota sporočilZdravstveni dom
11-Jun-22220Dom A
11-Jun-229Dom B
11-Jun-2210564Dom C
11-Jun-2293Dom D
11-Jun-22260Dom E
11-Jun-225001Dom F
13-Jun-22230Dom A
13-Jun-229Dom B
13-Jun-2210564Dom C
13-Jun-2296Dom D
13-Jun-22263Dom E
13-Jun-225079Dom F
12-Jun-22220Dom A
12-Jun-229Dom B
12-Jun-2210564Dom C
12-Jun-2293Dom D
12-Jun-22262Dom E
12-Jun-225010Dom 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.

 

 

  • 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.

     

1 Reply

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support

    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.