Calculating totals based on data subsets

[ScottA]

Hi All,

I have versioned roster data, and I get this by employee. E.g. Everyones roster is published (at differing times) and the data flows through. If a change is then made to someones roster, I get their whole roster of data again with the change. The changes are timestamped (and versioned as a quick reference). Basic example below.

Employee Number	First Name	Surname	Roster Group	Resource Group	Base	Date	Activity	Layover Location	Roster	Roster Version	Occurred When
001	Bob	Jones	Test	Under Training	AKL	20/03/2020	WORK		2020.03	1	14/03/2020 13:11
001	Bob	Jones	Test	Under Training	AKL	20/03/2020	LAYOVER	BHE	2020.03	1	14/03/2020 13:11
001	Bob	Jones	Test	Under Training	AKL	21/03/2020	WORK		2020.03	1	14/03/2020 13:11
001	Bob	Jones	Test	Under Training	AKL	22/03/2020	OFF		2020.03	1	14/03/2020 13:11
001	Bob	Jones	Test	Under Training	AKL	20/03/2020	WORK		2020.03	2	20/03/2020 18:57
001	Bob	Jones	Test	Under Training	AKL	20/03/2020	LAYOVER	WLG	2020.03	2	20/03/2020 18:57
001	Bob	Jones	Test	Under Training	AKL	21/03/2020	WORK		2020.03	2	20/03/2020 18:57
001	Bob	Jones	Test	Under Training	AKL	21/03/2020	LAYOVER	CHC	2020.03	2	20/03/2020 18:57
001	Bob	Jones	Test	Under Training	AKL	22/03/2020	WORK		2020.03	2	20/03/2020 18:57
002	John	Smith	Test	Under Training	WLG	20/03/2020	OFF		2020.03	1	14/03/2020 13:13
002	John	Smith	Test	Under Training	WLG	21/03/2020	OFF		2020.03	1	14/03/2020 13:13
002	John	Smith	Test	Under Training	WLG	21/03/2020	OFF		2020.03	1	14/03/2020 13:13
002	John	Smith	Test	Under Training	WLG	20/03/2020	OFF		2020.03	2	14/03/2020 14:26
002	John	Smith	Test	Under Training	WLG	21/03/2020	WORK		2020.03	2	14/03/2020 14:26
002	John	Smith	Test	Under Training	WLG	21/03/2020	WORK		2020.03	2	14/03/2020 14:26
002	John	Smith	Test	Under Training	WLG	20/03/2020	WORK		2020.03	3	14/03/2020 17:40
002	John	Smith	Test	Under Training	WLG	21/03/2020	WORK		2020.03	3	14/03/2020 17:40
002	John	Smith	Test	Under Training	WLG	21/03/2020	WORK		2020.03	3	14/03/2020 17:40

So at any given time, employee 1 might be up to version 4 but employee 2’s latest roster is still their first roster version because there are no changes. My ultimate vision is to step through these change timestamps and show change as time progresses which I’ll do with the play axis or similar.

At each timestamp I want to get each employees latest roster version, and then make calculations on each employees latest roster to arrive at a roster total. For example total hotel nights.

In the screenshots below, each row of the table is correct (I guess only because I’m displaying the table by employee), but the total is wrong because at the shown timestamp employee 2 has the biggest roster version and they have no hotel nights.

How do I get the measure to evaluate for each employee as I step through timestamps?

Appreciate all help and advice

*Latest Roster Version = CALCULATE(MAX('Roster Data'[Roster Version]),FILTER('Roster Data','Roster Data'[Occurred When]<=SELECTEDVALUE('Change Timestamp Summary'[Occurred When])))

*Hotel Nights = 
VAR LatestRosterVersion = [*Latest Roster Version]
RETURN
CALCULATE(COUNTROWS('Roster Data'),'Roster Data'[Layover Location]<>"",'Roster Data'[Roster Version]=LatestRosterVersion)

 

[ScottA]

Hi All,

I have versioned roster data, and I get this by employee. E.g. Everyones roster is published (at differing times) and the data flows through. If a change is then made to someones roster, I get their whole roster of data again with the change. The changes are timestamped (and versioned as a quick reference). Basic example below.

Employee Number	First Name	Surname	Roster Group	Resource Group	Base	Date	Activity	Layover Location	Roster	Roster Version	Occurred When
001	Bob	Jones	Test	Under Training	AKL	20/03/2020	WORK		2020.03	1	14/03/2020 13:11
001	Bob	Jones	Test	Under Training	AKL	20/03/2020	LAYOVER	BHE	2020.03	1	14/03/2020 13:11
001	Bob	Jones	Test	Under Training	AKL	21/03/2020	WORK		2020.03	1	14/03/2020 13:11
001	Bob	Jones	Test	Under Training	AKL	22/03/2020	OFF		2020.03	1	14/03/2020 13:11
001	Bob	Jones	Test	Under Training	AKL	20/03/2020	WORK		2020.03	2	20/03/2020 18:57
001	Bob	Jones	Test	Under Training	AKL	20/03/2020	LAYOVER	WLG	2020.03	2	20/03/2020 18:57
001	Bob	Jones	Test	Under Training	AKL	21/03/2020	WORK		2020.03	2	20/03/2020 18:57
001	Bob	Jones	Test	Under Training	AKL	21/03/2020	LAYOVER	CHC	2020.03	2	20/03/2020 18:57
001	Bob	Jones	Test	Under Training	AKL	22/03/2020	WORK		2020.03	2	20/03/2020 18:57
002	John	Smith	Test	Under Training	WLG	20/03/2020	OFF		2020.03	1	14/03/2020 13:13
002	John	Smith	Test	Under Training	WLG	21/03/2020	OFF		2020.03	1	14/03/2020 13:13
002	John	Smith	Test	Under Training	WLG	21/03/2020	OFF		2020.03	1	14/03/2020 13:13
002	John	Smith	Test	Under Training	WLG	20/03/2020	OFF		2020.03	2	14/03/2020 14:26
002	John	Smith	Test	Under Training	WLG	21/03/2020	WORK		2020.03	2	14/03/2020 14:26
002	John	Smith	Test	Under Training	WLG	21/03/2020	WORK		2020.03	2	14/03/2020 14:26
002	John	Smith	Test	Under Training	WLG	20/03/2020	WORK		2020.03	3	14/03/2020 17:40
002	John	Smith	Test	Under Training	WLG	21/03/2020	WORK		2020.03	3	14/03/2020 17:40
002	John	Smith	Test	Under Training	WLG	21/03/2020	WORK		2020.03	3	14/03/2020 17:40

So at any given time, employee 1 might be up to version 4 but employee 2’s latest roster is still their first roster version because there are no changes. My ultimate vision is to step through these change timestamps and show change as time progresses which I’ll do with the play axis or similar.

At each timestamp I want to get each employees latest roster version, and then make calculations on each employees latest roster to arrive at a roster total. For example total hotel nights.

In the screenshots below, each row of the table is correct (I guess only because I’m displaying the measure by employee in the table), but the total is wrong because at the shown timestamp employee 2 has the biggest roster version and they have no hotel nights.

How do I get the measure to evaluate for each employee as I step through timestamps?

Appreciate all help and advice

*Latest Roster Version = CALCULATE(MAX('Roster Data'[Roster Version]),FILTER('Roster Data','Roster Data'[Occurred When]<=SELECTEDVALUE('Change Timestamp Summary'[Occurred When])))

*Hotel Nights = 
VAR LatestRosterVersion = [*Latest Roster Version]
RETURN
CALCULATE(COUNTROWS('Roster Data'),'Roster Data'[Layover Location]<>"",'Roster Data'[Roster Version]=LatestRosterVersion)

[v-lid-msft · Accepted solution]

Hi @ScottA ,

We can use the following measure by using SUMX function to meet your requirement.

*Hotel Nights =

SUMX (

    VALUES('Roster Data'[Employee Number]),

    VAR LatestRosterVersion = [*Latest Roster Version]

    RETURN

        CALCULATE (

            COUNTROWS ( 'Roster Data' ),

            'Roster Data'[Layover Location] <> "",

            'Roster Data'[Roster Version] = LatestRosterVersion

        )

)

And here is the result:

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

[ScottA]

Hi @v-lid-msft 

This is perfect, thank you. I can see how VALUES() is creating a distinct list/table of employee_numbers, and then SUMX is allowing the original measure to SUM for each row of that table. 

I had been experimenting with SUMX but I just couldn't visualise how to get it to work.

Really appreciate it, cheers