Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Dear Power BI Community,
I would greatly appreciate some help with the following, which I'm trying to achive in DAX:
Situation
Objective
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.
My current measure is:
LatestStatus =
CALCULATE(
MAX( 'Table'[Status] ),
DateTable[Date] <= max(DateTable[Date])
)
Any advice is greatly appreciated
Solved! Go to 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
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
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
)
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.
Thank you @v-yalanwu-msft !
I ended up using the solution proposed by @PaulOlding .
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |