Running total formula doesn't work with additional measure placed on Legends

[tomekm]

Hello,

I have a running total measure for a value for all the months in 2022 that is a combination of the actual values (for jan. to may) and it’s estimated value for future months, and I want to color code the months so that anything present/past is shown in one color (“current”) and anything in the future is a different color (“LE”). For color coding, I created a formula and added it as Legend to show a 2 color distinction between the 2 options. However by doing this, the running total measure in the columns chart just computes Jan to May and then it restarts after May with a running total computed from June to Dec (but only if I leave the color coding formula on Legends. Once it’s removed, the running total works fine). Is there any way to fix the color coding for the running total so that regardless if its labelled as “current” or “LE”, the graph keeps showing the running total for all the months in 2022?

Here are my formulas:

Running Total =

CALCULATE(

    SUM('Table'[Value]),

    FILTER(

        ALLSELECTED('Table'[Period]),

        ISONORAFTER('Table'[Period], MAX('Table'[Period]), DESC)

    )

)

Current vs LE =

IF('Table'[Period]<=TODAY(),"Current", "LE")

Any help is much appreciated!

[v-yalanwu-msft · Accepted solution]

Hi, @tomekm ,

Measure can't be used as a legend, only column can be used as a legend, so you can share a simple file and want the output to be displayed, maybe using conditional format.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@tomekm , Try like

Running Total =

CALCULATE(

SUM('Table'[Value]),

FILTER(

ALL('Table'[Period]),

ISONORAFTER('Table'[Period], MAX('Table'[Period]), DESC)

)

)

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Can you share issue screenshot and expected value

[tomekm]

Thanks. I tried your formula but it didn't work for me.

But here's the basic sample data that I'm working with, and below are 2 screenshots:

Period	Value
2022-01	1200
2022-02	390
2022-03	570
2022-04	443
2022-05	831
2022-06	590

The desired output is the one below. Here I manually adjusted the color for the first 5 periods. But I would like to be able to have this automatically adjusted via a formula, placed on Legend.

The screenshot below is the wrong output that I currently get when I place my "Current vs LE" formula on Legends: