Graph not dipslaying measure correctly

[alycianw]

I have 3 columns in my data: Actuals, Budget and LE.
I want to have a bar graph that shows Actuals (when avaliable), Budget for Past Months and LE for Future months
Since there is data for past LE and future Budget it is showing for all months. I could not figure out a way to filter the Budget or LE by date in the FIlters Pane.


I tried to create a measure for Future LE and Past Budget to resovle the issue but nothing I try is working. These are the current DAX I am using and the results I am getting.

Future LE =
CALCULATE (
SUM('Data Center Data'[LE]),
'Data Center Data'[Date] >= TODAY()
)

Past Budget =
IF (
MAX('Data Center Data'[Date]) < TODAY(),
SUM('Data Center Data'[Budget]),
BLANK()
)

Any suggestions on an easier way to filter the Budget or LE by date or DAX suggestions would be great. Thank you so much!

[Anonymous · Accepted solution]

Hi @alycianw ,

Try the following expression

Dynamic Value = 
VAR CurrentDate = TODAY()
VAR Actuals = SUM('Data Center Data'[Actuals])
VAR Budget = SUM('Data Center Data'[Budget])
VAR LE = SUM('Data Center Data'[LE])
RETURN
    SWITCH(
        TRUE(),
        MAX('Data Center Data'[Date]) < CurrentDate, Actuals + Budget,
        MAX('Data Center Data'[Date]) >= CurrentDate, Actuals + LE,
        BLANK()  
    )

Please feel free to correct me and provide more information if I have misunderstood you!

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[alycianw · Accepted solution]

Resolved my issue by adding a new column identifying if it is a past or future month and based on that column it was able to filter out future months for budget and past months for LE. Thank you for the help

[Anonymous · Accepted solution]

Hi @alycianw ,

Try the following expression

Dynamic Value = 
VAR CurrentDate = TODAY()
VAR Actuals = SUM('Data Center Data'[Actuals])
VAR Budget = SUM('Data Center Data'[Budget])
VAR LE = SUM('Data Center Data'[LE])
RETURN
    SWITCH(
        TRUE(),
        MAX('Data Center Data'[Date]) < CurrentDate, Actuals + Budget,
        MAX('Data Center Data'[Date]) >= CurrentDate, Actuals + LE,
        BLANK()  
    )

Please feel free to correct me and provide more information if I have misunderstood you!

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[alycianw]

I am playing with it to see if I can get it to work. The issue I see with this one is that its making it one value. I need to be able to identify the specific columns as Budget or LE, it looks like this makes a new value so does not specify each.
Thank you for the help and suggestions this may get me in the right direction.