Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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"]

powerbi.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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
natabird3
Continued Contributor
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))
, 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.

View solution in original post

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

View solution in original post

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")
 
 
natabird3
Continued Contributor
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))
, 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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.