Sum of Value by distinct ID and Latest Date

[Pbiuserr]

Hello,

Lets assume I have table

ID       Date              Value

ID1     01.01.2022    10

ID1     01.02.2022    10

ID1     01.03.2022    20

ID1     01.04.2022    30

ID2     01.01.2022    20

Expected result - 50 (Value 30 for ID1 with date 1.04.2022 + Value 20 from ID2)

How to do that? Thank you in advance

[lukiz84 · Accepted solution]

Hi,

add a calculated column to your fact table:

IsLastDate :=

CALCULATE(
     MAX(facTable[Date]),
     factTable[ID] = EARLIER(factTable[ID]),
     ALL(factTable[Date]),
     ALL(factTable[Value])
) = factTable[Date]

then you can add a measure:

SumOfMax = 
CALCULATE(
     SUM(factTable[Value]),
     factTable[IsLastDate]
)

[Pbiuserr]

Hi,

Can you do that without calculating a new column?

[lukiz84]

Here's the solution without a calculated column (kind of ;))

VAR tempTbl =
   ADDCOLUMNS(
      yourTable,
      "@IsLastDate",
      CALCULATE(
          MAX(yourTable[Date]), yourTable[ID] = EARLIER(yourTable[ID]), ALL(yourTable)
       ) = yourTable[Date]
    )
RETURN
   SUMX(
       FILTER(tempTbl, [@IsLastDate]),
       [Value] 
   )

BR 

Lukas

[lukiz84]

sure, but it's a little early for my brain to do it 😄