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

[Anonymous]

Hi, I would appreciate help with the following situation:
- I have a 'Table' with 'Items' that have a 'Status' (value between 0 and 1) on various dates

- The dates are not continous and each 'Item' can have its status reported on different dates.
- I have a 'DateTable' with continious dates
I want to achieve the following:
- Firstly, for every date in the DateTable, and every Item, I need to find the most recent status. (Fill in the gaps)
- Secondly, if an Item has no status on an earlier date, Status should be counted as 0
- Lastly, I need a daily average across all Items (Applying whatever filters the user has set)

Sample Data:

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

Desired result:

Date	A	B	Average
01-Jan-22	0.2	0	0.1
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

[Anonymous]

Hi @Anonymous 

Use Pivot in Power Query Editor to return column[A] and column[B] .

Then replace null with 0 .

Then close & apply your changes and go back to Desktop pane , add these fields in a table visual . You will get a result like this .

Create a calculated column to count the average .

Average = ('Table'[A]+'Table'[B])/2

The final result is as shown below .

I have attached my pbix file , you can refer to it .

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

[Anonymous · Accepted solution]

Dear @Anonymous ,

Thank you! 
I was looking for a solution within DAX only, without the use of Power Query. 
And, in reality my 'Items' are 1000's of varying different IDs, resulting in a huge pivot, what I would like to avoid. 
I have received a working answer on another of my posts of the same question (the original one was originally flagged and removed, hence the double post).
DAX formula to return Average daily value using most recent available values 
I ended up using this DAX measure:

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

Thank you

[Anonymous]

Hi @Anonymous 

If your problem has been solved , please consider Accept it as the solution to help the other members find it more quickly.

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