cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sheidari81 Frequent Visitor
Frequent Visitor

Calculating Variance: one month vs prior month

I am struggling with the formula to calculate the variance between two months. I have a table that has FTE counts by month for the fiscal year. I need to track the variance by month. So each month will subtract the prior month FTE count to see if there was any change.  I've tried several Dax formulas but none of them seem to work.  

 

Input Table:

This is where the user inputs their FTE count by month, per person. 

 

 

PP Troubleshoot 1.JPG 

 

 

 

 

 

 

 

 

 

 

 

 

FY17 Changes:

This is what the calculation should do...take the variance compared to the prior month (see formula bar).

PP Troubleshoot 6.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Calendar Table:

Here is my calendar table in PowerPivot

PP Troubleshoot 5.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Calculated Measures:

Here are the 3 different calculated measures I tried and none replicate the results in the FY17 changes image.

 PP Troubleshoot 4.JPGPP Troubleshoot 3.JPGPP Troubleshoot 2.JPG

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
mattbrice Senior Member
Senior Member

Re: Calculating Variance: one month vs prior month

First off, your Calendar table is not a true calendar table that is required for the Time Intelligence functions to work properly.   Calendar table should every day date for full years and "Marked as Date Table".  Time Intelligence functions make some assumptions that require the full calendar.

Second, date functions work by shifting the visible dates in filter context which means the date must exist in the calendar to be a filter.  No dates are "created".

Third, how are the tables related?  Nice to see map + which fields are linked.

 

What you want to do is pretty easy if the model is setup correctly. From what i can tell of your screenshots you are really close.

 

View solution in original post

mattbrice Senior Member
Senior Member

Re: anksRe: Calculating Variance: one month vs prior month

If you have a standard calendar table with all dates, you just need to link it to the "fact" table column that also has dates ( your has FirstDay, LastDay, etc columns.).  You don't care if the fact table doesn't have all dates inclusive.  And usually visuals don't show dates were measure returns blank.

View solution in original post

4 REPLIES 4
sheidari81 Frequent Visitor
Frequent Visitor

Re: Calculating Variance: one month vs prior month

anyone?

mattbrice Senior Member
Senior Member

Re: Calculating Variance: one month vs prior month

First off, your Calendar table is not a true calendar table that is required for the Time Intelligence functions to work properly.   Calendar table should every day date for full years and "Marked as Date Table".  Time Intelligence functions make some assumptions that require the full calendar.

Second, date functions work by shifting the visible dates in filter context which means the date must exist in the calendar to be a filter.  No dates are "created".

Third, how are the tables related?  Nice to see map + which fields are linked.

 

What you want to do is pretty easy if the model is setup correctly. From what i can tell of your screenshots you are really close.

 

View solution in original post

sheidari81 Frequent Visitor
Frequent Visitor

anksRe: Calculating Variance: one month vs prior month

Thanks, @mattbrice.  I got it to work. This was very helpful.  

 

One question...If my data table is only capturing data by month, how would I link it to a calendar table that is at the day level?

mattbrice Senior Member
Senior Member

Re: anksRe: Calculating Variance: one month vs prior month

If you have a standard calendar table with all dates, you just need to link it to the "fact" table column that also has dates ( your has FirstDay, LastDay, etc columns.).  You don't care if the fact table doesn't have all dates inclusive.  And usually visuals don't show dates were measure returns blank.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)