Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
-----------------------------------------------------
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |