Count the number of Ids with decreases in a time series

[Anonymous]

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 

[Anonymous]

@Mikelytics thanks - I think that might be it!

[Mikelytics · Accepted solution]

HI @Anonymous 

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.

-----------------------------------------------------

LinkedIn