Calculate Avg for selected Date, take value from last 3 days if a value is missing for today.

[Anonymous]

Hi All,

I have been struggling to build this measure, If [Occupancy %] is Blank for Select Date (21/11/2021) , then look into last three Dates, If [Occupancy %] exists in Last Three Dates then take into Calculation.  

Average(95%, 75%, 88%) for 21/11/2021 is 86% . 

AREA	Date	Occupancy %
A	21/11/2021	95%
B	21/11/2021
C	21/11/2021	75%
D	21/11/2021
E	21/11/2021	88%
A	20/11/2021	97%
B	20/11/2021
C	20/11/2021	94%
D	20/11/2021	92%
E	20/11/2021	100%
A	19/11/2021	97%
B	19/11/2021
C	19/11/2021	80%
D	19/11/2021	96%
E	19/11/2021	90%
A	18/11/2021	100%
B	18/11/2021	62%
C	18/11/2021	63%
D	18/11/2021	89%
E	18/11/2021	98%
A	17/11/2021	82%
B	17/11/2021	88%
C	17/11/2021	64%
D	17/11/2021	71%
E	17/11/2021	97%

Desired Output for Date 21/11/2021:

Average(95%,  62%, 75%, 92%, 88%)  = 82% 

B and D are blank for 21/11/2021, so i have to take from previous available dates.

Thanks in Advance..

[amitchandak · Accepted solution]

@Anonymous , use a date table

Last Day Non Continuous =
var _1 = calculate(max(Table[Date]) , filter(allselected(Table), [AREA] = max(Table[AREA]) && 'Table'[Date] < max('Date'[Date])))
return
if(isblank([sales]) , calculate(sales, filter(all('Date'), 'Date' =_1)), [sales])

[amitchandak]

@Anonymous , Trya measure like below, with help from date table

Rolling 3 = if(isblank([Occupancy %]), CALCULATE(AverageX(values('Date'[Date]),[Occupancy %]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date])-1,-3,DAY)) ,[Occupancy %])

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

@amitchandak 

Thanks for replying, 

I dont want to calculate rolling,
I want to calculate the Average [Occupancy %] for Selected [Date] ,

But if [Occupancy %] is blank for Any [AREA] in the selected [Date] ,
then take previous Date [Occupancy %] to calculate the Average for Selected Date. 

You can see in the Data that [AREA] B and D are blank for the Date 21/11/2021, in this case i need to take 

[Occupancy %] for AREA B and D from previous Date 20/11/2021, or from 19/11/2021.

Thanks