Cumulative Budget Variance with 0 on first selected Date

[jaryszek]

Hello Guys,

this is my chart:

and the measure is simple:

Cumulative Budget Variance = 
VAR CurrentDate = MAX ( 'Dim_Date'[Date] )
RETURN
CALCULATE (
    [Budget Variance],
    FILTER (
        ALL ( 'Dim_Date'[Date] ),
        'Dim_Date'[Date] <= CurrentDate
    )
)

Budget Variance = [Sum Budget Amount] - [Actual Cost Budget Daily]

So what i want is to have 0 in a first selected date in a bar chart. 
How to do this in DAX?

Best,
Jacek

[MFelix]

Hi @jaryszek ,

Apologies for the question but what do you mean 0 on the first selected date in the bar chart?

The bart chart that you present I assume has the days on the axis, do you have any selection of dates to get this visualization?

Can you please elaborate a litle bit more on the context.

[jaryszek]

thanks! 

by 0 i want to have just empty there. It means that first day of selected dates within slicer should be empty always. 
I do not want to have budget variance there.

Best,
Jacek

[MFelix · Accepted solution]

Hi @jaryszek,

Try the following code:

Cumulative Budget Variance = 
VAR MinimumDate = MINX(ALLSELECTED('Dim_Date'[Date]), 'Dim_Date'[Date])
VAR CurrentDate = MAX ( 'Dim_Date'[Date] )
RETURN
IF( SELECTEDVALUE('Dim_Date') = MinimumDate, 0 
CALCULATE (
    [Budget Variance],
    FILTER (
        ALL ( 'Dim_Date'[Date] ),
        'Dim_Date'[Date] <= CurrentDate
    )
)
)

[jaryszek]

Ok didnt work but you pointed me to right direction:

New_Cumulative Budget Variance 2 = 
VAR MinimumDate =
    CALCULATE (
    MIN ( 'Dim_Date'[Date] ),
    ALLSELECTED ( 'Dim_Date'[Date] )
)
VAR CurrentDate =
    MAX ( 'Dim_Date'[Date] )
RETURN
IF (
    SELECTEDVALUE ( 'Dim_Date'[Date] ) = MinimumDate,
    0,
    CALCULATE (
        [Budget Variance],
        FILTER (
            ALLSELECTED ( 'Dim_Date'[Date] ),
            'Dim_Date'[Date] <= CurrentDate
        )
    )
)

Minx was not working because it took dates from entire Dim_Date table, not selected month. 

Thanks
Jacek

[MFelix]

Hi @jaryszek ,

It should give you the correct result but this can also depend on how you have your model working and the interactions between visuals and relationships.

Glad it pointed you to the correct option.

Don't forget to mark the correct answer so it can help others even if it is your own answer.