Forum Discussion
How get the last value on a given date
Hi all.
I have table:
that false. Would like to see the remainder from 12885,82625 as of 04.12.2019
More samples as I want to see:
I try next queries, but nothing correct worked:
Remainder:=
var a = CALCULATE(LASTNONBLANK('Table1'[RemDoc], 1),
FILTER(ALL('Date'),'Date'[DateKey] <= MAX('Table1'[DateKey])))
var b = IF((a<=0),0)
return a
******
Remainder:=
var suma = CALCULATE (SUM('Table1'[RemDoc]),
FILTER (ALL('Date'),'Date'[DateKey] <= MAX('Table1'[DateKey])))
var rem = IF((suma<0),0,suma)
return rem
******
Остаток:=
SUMX (VALUES ('Table1'[Partner]),
VAR LastBalanceDate = CALCULATE ( MAX ( Table1'[DateKey] ) )
RETURN
CALCULATE (
SUM ('Table1'[RemDoc]),
'Date'[DateKey] >= LastBalanceDate))
****
How to achieve the desired result?
Thanks for your helps.
11 Replies
Don't use functions inside CALCULATE() filters. They get impacted by the context transition. Define your filters as variables before using them in CALCULATE().
remember you need to control the filter context for Calculate(), otherwise it will only calculate it for the "current row"
lbendlin can you show an example?
Anonymous
What you have tried is running total, to return latest date value, try create this measure using lastdate():Measure = CALCULATE(SUM('Table1'[RemDoc]),LASTDATE('Table1'[Date]))Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
