Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

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

DateStatusItem
01-Jan-220.2A
02-Jan-220.3A
03-Jan-220.4A
04-Jan-220.5A
07-Jan-220.6A
10-Jan-220.8A
11-Jan-220.8A
14-Jan-220.9A
16-Jan-221A
02-Jan-220.2B
03-Jan-220.3B
10-Jan-220.4B
12-Jan-220.5B
14-Jan-220.6B
17-Jan-220.8B
19-Jan-220.9B
20-Jan-221B

 

My desired result is as follows:

 

DateABAverage
01-Jan-220.2 0.2
02-Jan-220.30.20.25
03-Jan-220.40.30.35
04-Jan-220.50.30.4
05-Jan-220.50.30.4
06-Jan-220.50.30.4
07-Jan-220.60.30.45
08-Jan-220.60.30.45
09-Jan-220.60.30.45
10-Jan-220.80.40.6
11-Jan-220.80.40.6
12-Jan-220.80.50.65
13-Jan-220.80.50.65
14-Jan-220.90.60.75
15-Jan-220.90.60.75
16-Jan-2210.60.8
17-Jan-2210.80.9
18-Jan-2210.80.9
19-Jan-2210.90.95
20-Jan-22111
21-Jan-22111

 

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

1 ACCEPTED SOLUTION
PaulOlding
Solution Sage
Solution Sage

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

PaulOlding_0-1657272041396.png

 

View solution in original post

4 REPLIES 4
PaulOlding
Solution Sage
Solution Sage

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

PaulOlding_0-1657272041396.png

 

Anonymous
Not applicable

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
Community Support
Community Support

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:

vyalanwumsft_0-1657259019631.png


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
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.