Returning data from the date nearest to that selected on the slicer

[BenArnold81]

Hello,

I've been wrestling with this problem for two days now and it's starting to make me ill!  I'm hoping someone can help.

I've Excel forums a lot to find answers, but never written on them.  I've only been learning Power BI for a week, but this forum has already proved useful. I apologise in advance if the way I set out my question is not standard or helpful.

I'll try to explain as clearly as I can what it is I want.

I'm producing a performance report, fed by data in a table ("Data"), alongside a calendar table ("Dates").  One of the measures I'm trying to generate a visual for will show the proportion of the workforce who have completed a certain course (in a donut chart).  This measure reference is "CHM 0026".  The value for the proportion of employees who have completed the course is taken as a snapshot the day after the end of the financial quarter.  E.g. the data that covers the period October to December 2018 (or Q3 2018/19 using our financial calendar) is ready to be input on 1st Jan 2019.  The table below shows that the value (or "actual") for this indicator for this period was 54.32%.

Ref	Period	Data due	Actual
CHM 0025	Q1 2018/19	01/07/2018	3.409544
CHM 0025	Q2 2018/19	01/10/2018	3.288903
CHM 0025	Q3 2018/19	01/01/2019	3.778834
CHM 0025	Q4 2018/19	01/04/2019	4.631608
CHM 0025	Q1 2019/20	01/07/2019	6.01
CHM 0025	Q2 2019/20	01/10/2019	5.807486
CHM 0025	Q3 2019/20	01/01/2020	4.088203
CHM 0025	Q4 2019/20	01/04/2020
CHM 0025	Q1 2020/21	01/07/2020
CHM 0025	Q2 2020/21	01/10/2020
CHM 0025	Q3 2020/21	01/01/2021
CHM 0025	Q4 2020/21	01/04/2021
CHM 0025	Q1 2021/22	01/07/2021
CHM 0025	Q2 2021/22	01/10/2021
CHM 0025	Q3 2021/22	01/01/2022
CHM 0025	Q4 2021/22	01/04/2022
CHM 0025	Q1 2022/23	01/07/2022
CHM 0025	Q2 2022/23	01/10/2022
CHM 0025	Q3 2022/23	01/01/2023
CHM 0025	Q4 2022/23	01/04/2023
CHM 0026	Q1 2018/19	01/07/2018	13.75
CHM 0026	Q2 2018/19	01/10/2018	34.5
CHM 0026	Q3 2018/19	01/01/2019	54.32099
CHM 0026	Q4 2018/19	01/04/2019	54.11765
CHM 0026	Q1 2019/20	01/07/2019	30.55556
CHM 0026	Q2 2019/20	01/10/2019	70.83333
CHM 0026	Q3 2019/20	01/01/2020	73.52941
CHM 0026	Q4 2019/20	01/04/2020
CHM 0026	Q1 2020/21	01/07/2020
CHM 0026	Q2 2020/21	01/10/2020
CHM 0026	Q3 2020/21	01/01/2021
CHM 0026	Q4 2020/21	01/04/2021
CHM 0026	Q1 2021/22	01/07/2021
CHM 0026	Q2 2021/22	01/10/2021
CHM 0026	Q3 2021/22	01/01/2022
CHM 0026	Q4 2021/22	01/04/2022
CHM 0026	Q1 2022/23	01/07/2022
CHM 0026	Q2 2022/23	01/10/2022
CHM 0026	Q3 2022/23	01/01/2023
CHM 0026	Q4 2022/23	01/04/2023

I need to be able to give the user the option of producing a report that reflects the situation on a given date, so they could retrospectively produce a report for three months ago, a year ago, etc. I don't want to do this in filters as they'll be locked down, so I'm using a slicer with a "before" option to act as a date picker, linked to my calendar table, which is linked to the "Data due" column in the above table.

I've used a measure to try to produce this:

MostRecentDate =

VAR SlicerDate =Max(Dates[Day])

Return

calculate(MAX(Data[Data due]),filter(all(Data[Data due]),Data[Data due]<=SlicerDate),filter(data,Data[Ref]="CHM 0026"))

This seems to work, as putting the result in a card shows 01,01,2019.

My next task is to extract the actual figure for that date for the ref in question.  For this, I have used another measure:

MostRecentActual =

calculate(SUM(Data[Actual]),filter(Data,Data[Data due]=[MostRecentDate]),FILTER(Data,Data[Ref]="CHM 0026"))

I hoped this would give me the single value as there's only one row that meets the two criteria, but instead it gives me the sum of the three dates for the ref that are on or before 01,01,2019, which is 102.57.

I've been looking at this for so long that I'm now completely lost and need a second or third pair of eyes.

Thanks very much.

Ben

[amitchandak · Accepted solution]

@BenArnold81 , Try a measure like

MostRecentActual =
VAR SlicerDate =MaxX(selectedvalues(Dates), Dates[Day])
Return
calculate(lastnonblankvalue(Data[Data due], SUM(Data[Actual])),filter(Data,Data[Data due]<=SlicerDate && Data[Ref]="CHM 0026"))

 or

MostRecentActual =
VAR SlicerDate =MaxX(selectedvalues(Dates), Dates[Day])
Return
calculate(lastnonblankvalue(Data[Data due], SUM(Data[Actual])),filter(all(Data),Data[Data due]<=SlicerDate && Data[Ref]="CHM 0026"))

I would suggest you to use the date table, as you going to consider every below the selected date, you might force to use all and in that case, you will end up using all on data.

[BenArnold81]

@amitchandra You might be a genius.

I haven't fully understood what you've done yet and I couldn't do eactly what you said - it wouldn't let me do MaxX(selectedvalues.... not sure why.

At the moment, I have this and it seems to be working for this measure:

MostRecentActual =

VAR SlicerDate = MaxX(Dates,Dates[Day])

return

CALCULATE(LASTNONBLANKVALUE(Data[Data due],SUM(Data[Actual])),filter(Data,Data[Data due]<=SlicerDate && Data[Ref]="CHM 0026"))

What is the "Lastnonblankvalue" doing in this measure?  Does it work because the dates in "Data due" are listed chronologically?

I'll let you know how I get on once I've tested it on a few different things.

Thanks again.

Ben

[Greg_Deckler]

@BenArnold81 - You will need to write a measure where you use SELECTEDVALUE to get the slicer value and then use ALL to override filter context and find the "near" value you want. This might be a Complex Selector situation. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

[BenArnold81]

@Greg_Deckler Thanks very much for responding.  Are you able to give more detail on how the functions you suggest would fit?  I may already have a solution for this, but I'm trying to learn as much as I can, so I'd really welcome alternatives.

Many thanks

Ben