CODEWITHSUNDEEP
powerbidax
user882670
user882670

Reputation:

Create table with summarization and latest value of the year

For each ClientNo I want the Type classification corresponding to the last date of each Year:

enter image description here

Thus, the table above should be summarized as:

enter image description here

So, somehow, we need two intermediate tables:

Then for each combination of Year and ClientNo, get the latest date for each year and finally get the Type classification.

Upvotes: 0

Views: 44

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

You should be able to do this in two steps along these lines:

Summary =
VAR MaxDates =
    SUMMARIZE (
        ADDCOLUMNS ( Table1, "Year", YEAR ( Table1[Date] ) ),
        Table1[ClientNo],
        [Year],
        "MaxDate", MAX ( Table1[Date] )
    )
RETURN
    SELECTCOLUMNS (
        MaxDates,
        "ClientNo", [ClientNo],
        "Year", [Year],
        "Type", LOOKUPVALUE (
            Table1[Type],
            Table1[ClientNo], [ClientNo],
            Table1[Date], [MaxDate]
        )
    )

In calculating the variable, we add a Year column and then calculate the maximal date corresponding to that year.

Then we take that table variable, pick out the ClientNo and Year columns, and look up what the Type corresponding to the MaxDate.

If Note: you want to keep the MaxDate column, replace

[...] SELECTCOLUMNS ( MaxDates, "ClientNo", [ClientNo], "Year", [Year], [...]

with

[...] ADDCOLUMNS ( MaxDates,  [...]

Upvotes: 1

Related Questions