Forum Discussion

sheidari81's avatar
sheidari81
Regular Visitor
9 years ago
Solved

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 wi...
  • mattbrice's avatar
    9 years ago

    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.

     

  • mattbrice's avatar
    mattbrice
    9 years ago

    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.