Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I'm really stuck on this one so I appreciate any help.
Say I have a time series table called Charges where the Charges can vary over time for any person. I want a create measure to count the number of people with a decrease in charge between the first and last dates. This will need to be affected by a date slicer.
Person | Date | Charge |
1 | 01/04/2022 | 320 |
1 | 02/04/2022 | 320 |
1 | 03/04/2022 | 320 |
1 | 04/04/2022 | 320 |
1 | 05/04/2022 | 320 |
1 | 06/04/2022 | 330 |
1 | 07/04/2022 | 330 |
2 | 01/04/2022 | 320 |
2 | 02/04/2022 | 320 |
2 | 03/04/2022 | 320 |
2 | 04/04/2022 | 340 |
2 | 05/04/2022 | 340 |
2 | 06/04/2022 | 340 |
2 | 07/04/2022 | 340 |
3 | 01/04/2022 | 300 |
3 | 02/04/2022 | 310 |
3 | 03/04/2022 | 300 |
3 | 04/04/2022 | 300 |
3 | 05/04/2022 | 300 |
3 | 06/04/2022 | 300 |
3 | 07/04/2022 | 300 |
4 | 03/04/2022 | 300 |
4 | 04/04/2022 | 300 |
4 | 05/04/2022 | 300 |
4 | 06/04/2022 | 300 |
4 | 07/04/2022 | 200 |
4 | 08/04/2022 | 200 |
I tried this sort of thing, but it's returning blank. The expected output would be 1 for person 4 who goes from a charge of 300 to 200 on the last day.
Number of people with decreases =
var gp = FILTER(
ADDCOLUMNS(
SUMMARIZECOLUMNS(
Charges[Person],
"First rate", FIRSTNONBLANK(Charges[Charge], TRUE()),
"Last rate", LASTNONBLANK(Charges[Charge], TRUE())),
"Change", [Last rate]<>[First rate]
),
[Last rate]<[First rate]
)
return
countrows(gp)
Here's a link to a test pbix https://drive.google.com/file/d/1ob-94FmdOAwr_NwaIgi2YxL0-G0Qk8Bs/view?usp=share_link
Solved! Go to Solution.
Can you please try
Number of people with decreases =
SUMX(
VALUES(Table[Person]),
var FirstDayRate = CALCULATE(MAX(Table[Charge],FIRSTDATE(Table[Date]))
var LastDayRate = CALCULATE(MAX(Table[Charge],LASTDATE(Table[Date]))
RETURN
IF(LastDayRate<FirstDayRate,1,0)
)
OR
Number of people with decreases =
SUMX(
VALUES(Table[Person]),
var FirstDayRate = CALCULATE(MAX(Table[Charge],FILTER(Table,FIRSTDATE(Table[Date])))
var LastDayRate = CALCULATE(MAX(Table[Charge],FILTER(Table,LASTDATE(Table[Date])))
RETURN
IF(LastDayRate<FirstDayRate,1,0)
)
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
Can you please try
Number of people with decreases =
SUMX(
VALUES(Table[Person]),
var FirstDayRate = CALCULATE(MAX(Table[Charge],FIRSTDATE(Table[Date]))
var LastDayRate = CALCULATE(MAX(Table[Charge],LASTDATE(Table[Date]))
RETURN
IF(LastDayRate<FirstDayRate,1,0)
)
OR
Number of people with decreases =
SUMX(
VALUES(Table[Person]),
var FirstDayRate = CALCULATE(MAX(Table[Charge],FILTER(Table,FIRSTDATE(Table[Date])))
var LastDayRate = CALCULATE(MAX(Table[Charge],FILTER(Table,LASTDATE(Table[Date])))
RETURN
IF(LastDayRate<FirstDayRate,1,0)
)
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |