Forum Discussion
How to report last value available
Hello,
I have one table called 'Cast' with:
Project Date bFP
A 01-01-2021 100
A 01-05-2021 120
Then I have the 'Calendar' Table linked to 'Cast' using 'data'.
The expected results is:
Date bFP Project
Jan-21 100 A
Feb-21 100 A
Mar-21 100 A
Apr-21 100 A
May-21 100 A
So for missing Date I need to see the last availabe value.
I elaborated the formula below:
CastBFP:=SUMX (
SUMMARIZE (
Cast,
Cast[sk_CastProject],
"myXBFP",
CALCULATE (
CALCULATE (
MAX ( Cast[bFP] ),
LASTNONBLANK ( Calendar[Data], CALCULATE ( MAX ( Cast[bFP] ) ) )
),
),
[myXBFP]
)
But results is:
Date bFP Project
Jan-21 100 A
Feb-21 A
Mar-21 A
Apr-21 A
May-21 100 A
So I do not have values for Feb, Marc and April.
How can I fix?
Thanks
Elisa
9 Replies
elyfiora , Try a measure like
measure =
var _max = calculate(max(Table[bFP]), filter( allselected(Date), Date[Date] = calculate(Max(Date[Date]), filter(allselected(Table), Table[Date] <=max(Table[date]) && Table[Project] = max(Table[project])))))Hello, thanks. Unfortunately it does not work. It shows only values for available dates and not for all the dates.
Hello, thanks.
Calendar contains all the dates, one for each day/month/year.
My results is (error in previous post):
Date bFP Project
Jan-21 100 A
Feb-21 A
Mar-21 A
Apr-21 A
May-21 120 A
Expected results are:
Date bFP Project
Jan-21 100 A
Feb-21 100 A
Mar-21 100 A
Apr-21 100 A
May-21 120 A
