Get last known value

Question

Hi all,&nbsp;I'm having problems with getting the last known value with my DAX. I now have:&nbsp;MeasureName = 
VAR maxdate = LASTDATE ( Date[Date] )

var result = 
CALCULATE (
    SUM ( Transactons[Amount] ) + 0,
    Transacties[Action]="Action 23",
    Transacties[Date] &lt;= maxdate,
    ALL(Datum)
)

RETURN result&nbsp;&nbsp;But this is doing exactly what I ask in the code; it gives me the SUM of amount for al specific dates BEFORE the max date. But that's not what I want; I want to have the latest available value, not a sum. But I'm stuck at fixing this.&nbsp;Example dataset:&nbsp;1 jan '20 - 1003 jan '20 - 80&nbsp;Desired output:1 jan '20 - 1002 jan '20 - 1003 jan '20 - 80&nbsp;Current output:1 jan '20 - 1002 jan '20 - 1003 jan '20 - 180&nbsp;Does anybody have an idea?

amitchandak · Answer

MiKeZZa , With a date table try something like this example
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date] &lt;=maxx(date,date[date])))
- CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date] &lt;=maxx(date,date[date])-1))
&nbsp;
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer : https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions https://www.archerpoint.com/blog/Posts/creating-date-table-power-bihttps://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184Appreciate your Kudos.

mikezza · Answer

This doesn't work, it gives strange values. Maybe because of gaps larger than 1 day??&nbsp;i'll check your documentation later.

anonymous · Answer

Hi&nbsp;MiKeZZa&nbsp;,&nbsp;&nbsp;MeasureName = 
VAR maxdate = MAX ( Date[Date] )

var result = 
CALCULATE (

MAX ( Transactons[Amount] ), FILTER (ALL(Transacties), Transacties[Action]="Action 23",
    Transacties[Date] = maxdate))

RETURN result

mikezza · Answer

I think you mean:&nbsp;MeasureName = 
VAR maxdate = MAX ( Date[Date] )

var result = 
CALCULATE (

MAX ( Transactons[Amount] ), FILTER (ALL(Transacties), Transacties[Action]="Action 23"),
    Transacties[Date] = maxdate)

RETURN result&nbsp;Right?&nbsp;This gives me only results on the day that there is a value.

az38 · Answer

Hi&nbsp;MiKeZZa&nbsp;
try to create a new table
Table 2 = 
ADDCOLUMNS(
    CALENDAR(MIN('Table'[Date]), MAX('Table'[Date])),
    "val", 
        CALCULATE(SUM('Table'[Value]), FILTER('Table', 'Table'[Date] &lt;= EARLIER([Date]) ))
    
)
&nbsp;

Forum Discussion

Get last known value

10 Replies

Related content

Closing value

remove duplicate value within a row

IF Negative value

Handling Null Values

DAX for calculating retroactive values