last available value

[graceste]

Hi, I'm having problem with a measure and hope you can help me.

I am receiving daily updates for different segments for example:

Segment	Value	Date
A	1	01.07.18
B	2	01.07.18
C	5	01.07.18
B	7	05.07.18
C	10	05.07.18
C	12	10.07.18
A	10	01.08.18
B	20	01.08.18
C	15	01.08.18
B	25	05.08.18
C	20	05.08.18
C	30	10.08.18

I have Date in a Slicer and expected result if I choose 10.08.18 would be following:

Segment	Value	Last avail.Value in month
A		10	last value from 1/8/18
B		25	last value from 5/8/18
C	30	30

and if choosen date in Slicer is 10.07.18, the result should be 

Segment	Value	Last avail.Value in month
A		1	last value from 1/7/18
B		7	last value from 5/7/18
C	12	12

[v-danhe-msft · Accepted solution]

Hi @graceste,

Based on my test, you could refer to below steps:

Create two measures:

Measure 2 = IF(MAX(Table1[Date])<=SELECTEDVALUE('Table'[Date]),1,0)

Measure1 = IF([Measure 2]=0,BLANK(),IF(CALCULATE(MAX(Table1[Date]),ALLEXCEPT(Table1,Table1[Segment]))= MAX(Table1[Date]),1,0))

Filter the value in Measure1.

Result:

You could also download the pbix to have a view.

https://www.dropbox.com/s/w3pnw3nnwjpbo4k/last%20available%20value.pbix?dl=0

Regards,

Daniel He

[graceste]

Hi @v-danhe-msft

thanks for your tip, it works

best regards

Grace