cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## How to calculate TY vs LY variance

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.

2 ACCEPTED SOLUTIONS
Continued Contributor

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))
, ALL('Calendar'))

Then to get the variance:
TY vs LY =
DIVIDE (
SUM ("Table Name"[Phys. Case]),
CALCULATE (
SUM ("Table Name"[Phys. Case]),
ALL ('Calendar')),
BLANK ())-1

And you should get your value.
Anonymous
Not applicable

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")

2 REPLIES 2
Anonymous
Not applicable

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")

Continued Contributor

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))
, ALL('Calendar'))

Then to get the variance:
TY vs LY =
DIVIDE (
SUM ("Table Name"[Phys. Case]),
CALCULATE (
SUM ("Table Name"[Phys. Case]),
ALL ('Calendar')),
BLANK ())-1

And you should get your value.

Announcements