New User - Variance problem

[Mark-Roberts]

Hi All,

New to Power BI, and am having trouble with what I thought would be a simple isssue...

I have some Measures :-

TotalOrders = SUMX(emea_pbi_salesorders,emea_pbi_salesorders[unitprice] * emea_pbi_salesorders[traderquantity])

Orders Current Year = CALCULATE ( [TotalOrders], 'Date'[Year] = YEAR(TODAY()))

Orders Previous Year = CALCULATE([TotalOrders], SAMEPERIODLASTYEAR('Date' [Date]))

Variance = DIVIDE([Orders Current Year],[Orders Previous Year], BLANK())

Which produces...

When I change the variance measure to :-

Variance = DIVIDE([Orders Current Year],[Orders Previous Year], BLANK()) -1

I am getting the previous year, which isnt what I want.
Any advice on how to fix this?

Thanks in advance.

Kind Regards,

Mark

[Anonymous · Accepted solution]

HI @Mark-Roberts,

You can add an if statement to your expression to check if the current value is 'not blank' to prevent the calculation expand to the not-required ranges.

Variance =
IF (
    [Orders Previous Year] <> BLANK (),
    DIVIDE ( [Orders Current Year], [Orders Previous Year], BLANK () ) - 1
)

Regards,

Xiaoxin Sheng