Dynamically calculate sum for current and historical data

Hi, I am trying to develop a measure that returns the amount of money held in unresolved files. The metric needs to show the values at any point in time, and not just the live data. To facilitate...

xifeng_L

2 years ago

Hi 13havya ,

You can try below measure.

$ Debt = 
LASTNONBLANKVALUE(
    Dim_Date[Date], // use the row label field of matrix.

    VAR Live = CALCULATE (SUMX(FILTER(Fact table, report = "Live" && {conditions}), debt_amt), userelationship(timestamp, date))

    Var Historical = CALCULATE (SUMX(FILTER(Fact table, report = "Historical" && {conditions}), debt_amt)),userelationship(timestamp, date))

    RETURN 

    IF (YEAR(Max(Dim_Date[Date])) = YEAR(TODAY) && MONTH(MAX(Dim_Date[Date])) = MONTH(TODAY), Live, Historical)
)

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

$ Debt = LASTNONBLANKVALUE( Dim_Date[Date], // use the row label field of matrix. VAR Live = CALCULATE (SUMX(FILTER(Fact table, report = "Live" && {conditions}), debt_amt), userelationship(timestamp, date)) Var Historical = CALCULATE (SUMX(FILTER(Fact table, report = "Historical" && {conditions}), debt_amt)),userelationship(timestamp, date)) RETURN IF (YEAR(Max(Dim_Date[Date])) = YEAR(TODAY) && MONTH(MAX(Dim_Date[Date])) = MONTH(TODAY), Live, Historical) )

Forum Discussion

Dynamically calculate sum for current and historical data