DAX formula to return Average daily value using most recent available values

[Anonymous]

Dear Power BI Community,

I would greatly appreciate some help with the following, which I'm trying to achive in DAX:

Situation

• I have various 'Items' that have a status (value between 0 and 1) on various days.
• The days are not continous; there can be gaps. Also, the dates could be different for each 'Item'
• In theory, the Status increases over time. However, this might not always be the case.
• I have a DateTable with continious dates

Objective

• Firstly, for every date, I need to find the most recent status for each item, and report it against that specific day. Basically 'fill in the gaps'.
• Secondly, I need to average the Status of all Items on that day; applying whatever filters/slicer could be set by the user.

My Data is as follows

Date	Status	Item
01-Jan-22	0.2	A
02-Jan-22	0.3	A
03-Jan-22	0.4	A
04-Jan-22	0.5	A
07-Jan-22	0.6	A
10-Jan-22	0.8	A
11-Jan-22	0.8	A
14-Jan-22	0.9	A
16-Jan-22	1	A
02-Jan-22	0.2	B
03-Jan-22	0.3	B
10-Jan-22	0.4	B
12-Jan-22	0.5	B
14-Jan-22	0.6	B
17-Jan-22	0.8	B
19-Jan-22	0.9	B
20-Jan-22	1	B

My desired result is as follows:

Date	A	B	Average
01-Jan-22	0.2		0.2
02-Jan-22	0.3	0.2	0.25
03-Jan-22	0.4	0.3	0.35
04-Jan-22	0.5	0.3	0.4
05-Jan-22	0.5	0.3	0.4
06-Jan-22	0.5	0.3	0.4
07-Jan-22	0.6	0.3	0.45
08-Jan-22	0.6	0.3	0.45
09-Jan-22	0.6	0.3	0.45
10-Jan-22	0.8	0.4	0.6
11-Jan-22	0.8	0.4	0.6
12-Jan-22	0.8	0.5	0.65
13-Jan-22	0.8	0.5	0.65
14-Jan-22	0.9	0.6	0.75
15-Jan-22	0.9	0.6	0.75
16-Jan-22	1	0.6	0.8
17-Jan-22	1	0.8	0.9
18-Jan-22	1	0.8	0.9
19-Jan-22	1	0.9	0.95
20-Jan-22	1	1	1
21-Jan-22	1	1	1

So far, I was only able to fill in the gaps.

• However, so far, I'm finding the maximum value on any date on or before the date in my DateTable. While I actualy need to find the most recent value, regardless if this is max value
• And I cannot figure out how to then succesfully AVERAGE the status across all Items for each day.
• I'm experimenting by doing the MAX first, and then do an AVERAGE on that result, with no success

My current measure is:

LatestStatus =
CALCULATE(
MAX( 'Table'[Status] ),
DateTable[Date] <= max(DateTable[Date])
)

Any advice is greatly appreciated

[PaulOlding · Accepted solution]

Hi @Anonymous 

Here's a measure to get your desired result.

Average = 
AVERAGEX(
    CALCULATETABLE(VALUES(StatusTable[Item]), ALLEXCEPT(StatusTable, StatusTable[Item])),
    CALCULATE(
        LASTNONBLANKVALUE('Date'[Date], MAX(StatusTable[Status])),
        'Date'[Date] <= MAX('Date'[Date])
        )
    )

As it uses LASTNONBLANKVALUE if the Status value goes down that will be reflected in the results.  That's very close to what you had already.  The extra part is an AVERAGEX to iterate all the selected Items and get the value for each, and return the average of them.

I changed your example data to test that out.  I made Status for A on 11-Jan = 0.7

[Anonymous]

Thank you @PaulOlding ! 
This is excellent and exactly what I was looking for. 
The Status going down scenario is indeed something that could potentially happen, so this meaure reflects the acurate desired result. I made a minor modification to also replace 'null' to 0; considering a status of 0 if there has not been any status yet.

Average = 
AVERAGEX(
    CALCULATETABLE(VALUES('Table'[Item]), ALLEXCEPT('Table', 'Table'[Item])),
    CALCULATE(
        LASTNONBLANKVALUE('DateTable'[Date], MAX('Table'[Status])),
        'DateTable'[Date] <= MAX('DateTable'[Date])
        )+0
    )

[v-yalanwu-msft]

Hi, @Anonymous ,

You need create another table about A and B,

Item = VALUES('Table'[Item])

Then create two measure suah as:

Measure = var _a= CALCULATE(SUM('Table'[Status]),FILTER(ALLSELECTED('Table'),[Date]=MAX('Table'[Date])&&[Item]=MAX('Item'[Item])))
var _max=CALCULATE(MAX('Table'[Date]),FILTER(ALLSELECTED('Table'),[Item]=MAX('Item'[Item])&&[Date]<MAX('Table'[Date])))
return IF(_a=BLANK(),CALCULATE(SUM('Table'[Status]),FILTER(ALLSELECTED('Table'),[Item]=MAX('Item'[Item])&&[Date]=_max)),_a)

aver = IF(HASONEFILTER('Item'[Item]),[Measure],DIVIDE( SUMX(FILTER('Item',[Item] in VALUES('Item'[Item])),[Measure]), COUNTX(FILTER('Item',[Measure]<>BLANK()),[Item])))

 The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you @v-yalanwu-msft !
I ended up using the solution proposed by @PaulOlding .