topic Sum of latest values based on the latest distinct value of another column in DAX Commands and Tips

Sum of latest values based on the latest distinct value of another column

AloisOh — Fri, 31 Dec 2021 04:43:20 GMT

Hi everyone,

For context, the title column is named after project sites and the worker and staff columns indicate the strength at the point of time. How can I sum the latest worker and staff values from all sites?

Regards,
Alois

Re: Sum of latest values based on the latest distinct value of another column

amitchandak — Fri, 31 Dec 2021 05:12:40 GMT

AloisOh , do you have the site and date?

You need to try a measure like

Measure =
VAR __id = MAX ('Table'[site] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[site] = __id )
CALCULATE ( Sum ('Table'[Worker] ), VALUES ('Table'[site] ),'Table'[site] = __id,'Table'[Date] = __date )

Re: Sum of latest values based on the latest distinct value of another column

smpa01 — Fri, 31 Dec 2021 15:10:02 GMT

AloisOh How can I sum the latest worker and staff values from all sites _ what indication the table has that defines latest? Is there a Date column or somehing else?

Re: Sum of latest values based on the latest distinct value of another column

Anonymous — Tue, 04 Jan 2022 03:10:03 GMT

Hi AloisOh,

As smpa01 mentioned, we couldn't find the columns which can distinguish the time sequence, like Date or others. I think the time sequenc is distinguished according to the sequence of the same site. Here I suggest you to add an Index column by Group by Power Query.

Firstly, Group all rows by [Title] in Power Query.

Go to Advanced editor and add a new line of code that can generate indices in all the previously grouped tables.

Indexed = Table.TransformColumns(#"Grouped Rows", {{"Rows", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}})

Then expand your table and rename column names.

For reference: How to create group index with Power Query or R

Measure:

Sum of Latest Worker = VAR _SUMMARIZE = SUMMARIZE ( 'Table', 'Table'[Title], "MAXINDEX", CALCULATE ( MAX ( 'Table'[GroupIndex] ) ), "LATESTWORKER", CALCULATE ( SUM ( 'Table'[Worker] ), FILTER ( 'Table', 'Table'[GroupIndex] = MAX ( 'Table'[GroupIndex] ) ) ) ) RETURN SUMX ( _SUMMARIZE, [LATESTWORKER] )

Sum of Latest Staff = VAR _SUMMARIZE = SUMMARIZE ( 'Table', 'Table'[Title], "MAXINDEX", CALCULATE ( MAX ( 'Table'[GroupIndex] ) ), "LATESTSTAFF", CALCULATE ( SUM ( 'Table'[Staff] ), FILTER ( 'Table', 'Table'[GroupIndex] = MAX ( 'Table'[GroupIndex] ) ) ) ) RETURN SUMX ( _SUMMARIZE, [LATESTSTAFF] )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.