Create measure - count of the latest status

[choy]

Hi All, 

I would like to seek your help to create a measure for waterfall chart, by count how many ID in each Stage (taken the latest/max date). Thank you.

ID	Stage	Date
1	A	2022-12-28T14:56:10
2	A	2022-12-07T11:22:27
2	B	2022-12-09T09:26:03
3	A	2022-10-24T12:01:08
3	B	2022-11-10T11:42:02
3	C	2022-11-24T10:05:36
Result
Count Latest Stage in A	Count Latest Stage in B	Count Latest Stage in C
1	1	1

[Mark_Little]

Hi,

Me personally would look at flagging the this in the script and it would give you a field to count in the set analysis.

LEFT JOIN (Table)

LOAD

ID,

MAX(Date),

1 as MaxFlag

Resident Table

Group By ID;

The you have the dimension on of stage and Count(MaxFlag)

[choy]

Any other way  I can do it on measure directly?

[MayilVahanan]

Hi

Try like this

Count Latest Stage in A:

=Count({<Stage={'A'}, Date1={"$(=Max({<Stage={'A'}>}Date1))"}>}DISTINCT ID)

Similarly for remaining things

Hope it helps

[MayilVahanan · Accepted solution]

Suppose, if Date is not Date format, then try like below

LOAD *, Timestamp(Timestamp#(Date, 'YYYY-MM-DDTHH:mm:SS')) as Date1 INLINE [
ID, Stage, Date
1, A, 2022-12-28T14:56:10
2, A, 2022-12-07T11:22:27
2, B, 2022-12-09T09:26:03
3, A, 2022-10-24T12:01:08
3, B, 2022-11-10T11:42:02
3, C, 2022-11-24T10:05:36
];

And then, use this exp: =Count({<Stage={'A'}, Date1={"$(=Max({<Stage={'A'}>}Date1))"}>}DISTINCT ID)

[choy]

I followed your solution but the result is 0 ?