CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Rebecca
Rebecca

Reputation: 67

How to stop showing a running total in Power BI

I have a running total in power BI as a line graph.

I want to only show my running total line up to the most recent months data (september 22), in order to get the flat line off the visualisation, so I can forecast the rest of the line.

My cumulative total measure is : '''Actual Spend YTD = CALCULATE ( SUM ( CombinedUsageFact[TargetCost] ),

FILTER (
    ALLSELECTED ( MonthNumDim[DateMonth]),
    ISONORAFTER ( MonthNumDim[DateMonth], MAX ( MonthNumDim[DateMonth]  ), DESC )
)

)'''

I need my axis to display up to year end (march 2023) like it does.

What can I do to stop my cumulative line at the most recent data?

Thanks

Would attach images/sample but it says there's a problem with the server!

Upvotes: 0

Views: 1201

Answers (1)

Mark Wojciechowicz
Mark Wojciechowicz

Reputation: 4477

To get the cumulative calculation to stop on the last period with data, filter the last date by the fact table:

Actual Spend YTD =
VAR maxDate =
    CALCULATE ( MAX ( MonthNumDim[DateMonth] ), CombinedUsageFact )
RETURN
    CALCULATE (
        SUM ( CombinedUsageFact[TargetCost] ),
        FILTER (
            ALLSELECTED ( MonthNumDim[DateMonth] ),
            ISONORAFTER ( MonthNumDim[DateMonth], maxDate, DESC )
        )

Upvotes: 1

Related Questions