Forum Discussion
last available value
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 |
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
2 Replies
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