How to calculate TY vs LY variance

[Anonymous]

Hi everyone,

I am using three columns of data:

1. Year [2017,2018,2019]
2. Date [Jan,Feb,March...etc]
3. Phys. Cases [aka "Total Sales"]

I am looking for a New Measure formula to help me find the variance in 'Phys Cases' 2018 vs 2019 and by month.

Any ideas? Thank you for your help.

[natabird3 · Accepted solution]

First create a new table calendar (if not already done one).

Calendar =

GENERATE (

CALENDAR ( DATE ( 2015, 1, 1 ), DATE ( 2020, 12, 30 ) ),

VAR currentDay = [Date]

VAR day = DAY( currentDay )

VAR month = MONTH ( currentDay )

VAR year = YEAR ( currentDay )

VAR month_name = FORMAT(DATE(year,month,1),"MMMM")

RETURN ROW (

"day", day,

"month", month,

"month_name", month_name,

"year", year )

)

Then try this for the actual number difference:

PY = CALCULATE(SUM("Table name"(Phys. Case))

, DATEADD('Calendar'[date], -1, YEAR)

, ALL('Calendar'))

Then to get the variance:

TY vs LY =

DIVIDE (

SUM ("Table Name"[Phys. Case]),

CALCULATE (

SUM ("Table Name"[Phys. Case]),

DATEADD ('Calendar'[Date],-1, YEAR),

ALL ('Calendar')),

BLANK ())-1

And you should get your value.

[Anonymous · Accepted solution]

I have seen on many boards that creating a new table calendar is the suggested route to take, however with limited Power BI knowledge, I had trouble creating a new table calendar in time to meet my deadline. I was able to create the following workaround:

(1) Create three new meaures to filter Physical Cases by Year:

• New Measure 2: 2018 = CALCULATE(SUM('Table'[Phys. Case]),FILTER('Table','Table'[Year]=2018))
• New Measure 3: 2019 = CALCULATE(SUM('Table'[Phys. Case]),FILTER('Table','Table'[Year]=2019))

(2) Find Physical Case Variance between each year

• '18 vs '19 +/-VAR = 'Table'[2019]-'Table'[2018]

(3) Find % Variance between each year* Included IFERROR to remove Infinity results

• '18 vs '19 %VAR = IFERROR('Table'[2019]/'Table'[2018]-1,"N/A")

[natabird3 · Accepted solution]

First create a new table calendar (if not already done one).

Calendar =

GENERATE (

CALENDAR ( DATE ( 2015, 1, 1 ), DATE ( 2020, 12, 30 ) ),

VAR currentDay = [Date]

VAR day = DAY( currentDay )

VAR month = MONTH ( currentDay )

VAR year = YEAR ( currentDay )

VAR month_name = FORMAT(DATE(year,month,1),"MMMM")

RETURN ROW (

"day", day,

"month", month,

"month_name", month_name,

"year", year )

)

Then try this for the actual number difference:

PY = CALCULATE(SUM("Table name"(Phys. Case))

, DATEADD('Calendar'[date], -1, YEAR)

, ALL('Calendar'))

Then to get the variance:

TY vs LY =

DIVIDE (

SUM ("Table Name"[Phys. Case]),

CALCULATE (

SUM ("Table Name"[Phys. Case]),

DATEADD ('Calendar'[Date],-1, YEAR),

ALL ('Calendar')),

BLANK ())-1

And you should get your value.