Sum of Latest Known Values for Measure

[Dr0idy]

I have a data table with the columns below. For each date the Care Homes may or may not have submitted their value for accumulative deaths. I would like to create a measure that is able to be plotted over time and sliced by care home. The measure needs to get the sum of latest available values (if deaths have not been submitted for that day for a particular care home it would fall back to the last known value).

Date

Care Home

Accumulative Deaths

Really stuck on this.

[Dr0idy · Accepted solution]

I posted the same issue in r/PowerBI and u/Arklur responded with the following which worked for me.

---

---

I think I got you. First I'll copy paste my dummy data I worked with, then explain them in more details:

PQ code for sample data

// Data
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtAzMAUiJR0lRyA2NFCK1UETdwJiI0xhZ5AwmnIzmDFGmOIgY4xRhc1hys1RxS2gytFcA+RCrDUBmhMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, CareHome = _t, AccumlativeDeaths = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"CareHome", type text}, {"AccumlativeDeaths", Int64.Type}})
in
    #"Changed Type"

DAX code for DateTable

(This isn't really the best approach to make one, but...I have a quite complex one and just removed what is unnecessary for me now, didn't really rewrite the whole thing, obviously using MINX here is a dumb move...anyway, just have a date table :))

DateTable = 
VAR vMinYear =
    YEAR (
        MINX (
            {
                MIN ( Data[Date] )
            };
            [Value]
        )
    )
VAR vMaxYear =
    YEAR (
        MAXX (
            {
                MAX ( Data[Date] )
            };
            [Value]
        )
    )
RETURN
    CALENDAR ( DATE ( vMinYear; 1; 1 ); DATE ( vMaxYear; 12; 31 ) )

Don't forget to create the relationship between the 2 tables, using the Date columns.

DAX measures

SumAccumulativeDeath = 
VAR vCurrDate = MAX ( DateTable[Date] )
RETURN
    CALCULATE (
        SUMX ( 
            ADDCOLUMNS (
                SUMMARIZE (
                    Data;
                    Data[CareHome]        
                );
                "cLastKnownAccumulativeDeath";
                VAR vLastReportedDate = 
                    CALCULATE ( 
                        MAX ( Data[Date] );
                        Data[Date] <= vCurrDate
                    )
                RETURN
                    CALCULATE (
                        SUM ( Data[AccumlativeDeaths] );
                        Data[Date] = vLastReportedDate
                    )
            );
            [cLastKnownAccumulativeDeath]
        );
        CROSSFILTER (
            Data[Date];
            DateTable[Date];
            None
        )
    )

HasDataRows = INT ( NOT ( ISEMPTY ( Data ) ) )

Result

https://i.imgur.com/1jFtuaA.png

Explanation

So...I'm going to be honest, I don't understand 100% why this works. I wanted to make this without using the CROSSFILTER, because with it, you need to use the HasDataRows measure as a filter for the visual (=1 condition), so only those dates show up which you have data for. What the measure does is pretty much what you need. For all CareHome, calculate the last time they reported the deaths, then grab the value for that date (I used SUM, but in case of you get duplicate data, you can use MAX, might make more sense), then SUMX them up.

Filters also work, you can filter on Data[CareHome] or DateTable[Date], according to my testing everything is working as expected --> https://i.imgur.com/5IusBmI.png.

What is really-really important, is not to forget to set that filter (using the HasDataRows measure) on the visual, because of you don't set it, you will get a bunch of future data, assuming your date table runs from 01.01 to 12.31 --> https://i.imgur.com/RrKZ7Jx.png.

As you may have noticed, there is no value for 2020.05.09, if you need to see all dates between the MIN ( Data[Date] ) and MAX ( Data[Date] ) then...you need to adjust a logic a little bit.

EDIT: /u/Data_cruncher, /u/itsnotaboutthecell sorry for the summoning. If you have the time, could you elaborate on my solution? I think it's more hacky than it should be, I'm quite sure there is a more elegant way of doing this.

EDIT2: Thanks to @Anonymous_cruncher, there is probably a better, more performant measure to achieve the same goal:

SqlbiSolution = 
VAR MaxDate =
    MAX ( DateTable[Date] )
VAR LastDates =
    ADDCOLUMNS (
        CALCULATETABLE (
            VALUES ( Data[CareHome] );
            ALL ( DateTable )
        );
        "LastDay"; CALCULATE (
            MAX ( Data[Date] );
            ALL ( DateTable );
            DateTable[Date] <= MaxDate
        )
    )
VAR LastDatesWithLineage =
    TREATAS (
        LastDates;
        Data[CareHome];
        DateTable[Date]
    )
VAR Result =
    CALCULATE (
        SUM ( Data[AccumlativeDeaths] );
        LastDatesWithLineage
    )
RETURN
    Result

[AlB]

Hi @Dr0idy 

You should create a date table. Then the measure is relatively simple:

Measure =
VAR latest_date_ =
    MAX ( Table1[Date] )
RETURN
    CALCULATE ( SUM ( Table1[CummulativeDeaths] ), Table1[Date] = latest_date_ )

You can then for instance use a chart visual with CareHome in the axis and Measure in values

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[Dr0idy]

@AlB 

Thanks for the quick response. I tried what you had suggested. When I put it on the graph it does not plot properly though. I should see a constant incline with no dips but instead I get the graph below. If it is always falling back to the last value I dont think this should happen. I have checked the data. There are minor issues that need remedied but nothing that explains a drop of 40.

[AlB]

@Dr0idy 

If you're going to use Date in the axis instead of CareHome as I suggested, and assuming the cumulative values are correct, you can simply take the max:

Measure2 =
SUMX(DISTINCT(Table1[CareHome]), CALCULATE(MAX(Table1[CumulativeDeaths]), ALL(DateTable[Date])))

 Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[Dr0idy]

@AlB 

I am still unable to get this to show what I want it to. Although this might just be me missing something.

Still getting the graph below when using the new measure you specified.

[amitchandak]

@Dr0idy ,Can you share sample data and sample output.

[Dr0idy]

@amitchandak @AlB 

here is (what i hope is) a clearer explanation of my issue.

I have data that looks something like :

Date Care Home Accumulative Deaths

06/05/2020	A	12
06/05/2020	B	3
05/05/2020	A	10
05/05/2020	B	2
05/05/2020	C	20

What all of the suggestions so far have done is for 06/05/2020 shown the deaths as 15 and for 05/05/2020 shown the deaths as 22.

What I want is to be able to create a line chart with Date as the x axis that shows 06/05/2020 deaths as 25 (using the last known value care home C provided as they did not submit on 06/05/2020) and 22 on 05/05/2020. I also need this chart to be able to be sliced by care home to show a chart for the particular care home.