Question about Variables (VAR)

[Anonymous]

Hi!
I have a calculated table that tells me if the client has sold more than 25K in the last 3 Quarters.

When I use variables, it doesn't work, when I don't use variables, it does....
Why??? It looks ugly without the variables...
With Variables:

Normalize = 
VAR Qm1 =
    CALCULATE (
        [SO Net],
        PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) )
    )
VAR Qm2 =
    CALCULATE (
        [SO Net],
        PREVIOUSQUARTER ( PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) ) )
    )
VAR Qm3 =
    CALCULATE (
        [SO Net],
        PREVIOUSQUARTER (
            PREVIOUSQUARTER ( PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) ) )
        )
    )
RETURN
        SUMMARIZECOLUMNS('DB'[End User Name (or Next Purchasing Entity)],
        "Normalize",
            SWITCH (
                TRUE (),
                CALCULATE (
        [SO Net],
        PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) )
    ) > 25000
                    && CALCULATE (
        [SO Net],
        PREVIOUSQUARTER ( PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) ) )
    ) > 25000
                    && CALCULATE (
        [SO Net],
        PREVIOUSQUARTER (
            PREVIOUSQUARTER ( PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) ) )
        )
    ) > 25000, "Large Clients (Exclude to normalize)",
                "Other Clients"
            )
    )

 Without Variables:

Normalize = 
SUMMARIZECOLUMNS (
    'DB'[End User Name (or Next Purchasing Entity)],
    "Normalize",
        SWITCH (
            TRUE (),
            CALCULATE ( [SO Net], PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) ) ) > 25000
                && CALCULATE (
                    [SO Net],
                    PREVIOUSQUARTER ( PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) ) )
                ) > 25000
                && CALCULATE (
                    [SO Net],
                    PREVIOUSQUARTER (
                        PREVIOUSQUARTER ( PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) ) )
                    )
                ) > 25000, "Large Clients (Exclude to normalize)",
            "Other CLients"
        )
)

[Anonymous · Accepted solution]

Hi @Anonymous ,

Looking at the two measures you wrote separately (with and without variables), the first measure with variables, although variables are defined, but does not use it in the later calculations. It seems that the formulas are the same except for the defined variable in these two measures. It is reasonable to assume that the two measures will return the same results...
You can check the following link to learn more about the use and caution of variables. Variables are calculated within the scope in which they are written, and then the result of them is stored and used in the rest of the expression.

Use variables to improve your DAX formulas

Caution When Using Variables in DAX and Power BI

Best Regards

[lbendlin]

You are defining the variables but are not using them anywhere?