This topic contains 1 reply, has 2 voices, and was last updated by 
-
Posts
-
I am working on a dashboard and am attempting to provide YOY balance comparisons. My dashboard has a slicer that allows the user to choose from various month end date views of the data. So for example, they could choose 09/30/15 as a view or could choose 08/31/15 as a view which would update the tables, graphs, charts, etc. to report the data for the date that was chosen. My understanding of YOY measures (DATEADD, SAMEPERIODLASTYEAR functions) is that the pivot table would need to include the full gamut of dates from the starting period to the ending period being evaluated for the YOY to work correctly. This is contrary to my setup which is a one month at a time view. I have looked for alternatives and found Marco Russo’s method which I incorporated only to have it return the balances for the period in view (i.e. not the prior year period). Based on this result, I have a feeling it is constrained in the same manner as the DAX time intelligence functions in that all of dates in between need to be in view to get the intended result. Just curious if anyone has experience with this and/or has found a solution.
Hi Mike,
I think you may have been steered in the wrong direction: IF you have a calendar table (which complies with the four rules I will list below) and that calendar table is related to your data table on a date column, your calculations for YOY calculations will work.
Four Rules for a Calendar table:
Calendar table has at least a column of date type.
Dates are contiguous in the Calendar table (no gaps).
Dates in the calendar table do not have a time component other than all zeroes.
Calendar table is marked as Date Table (Power Pivot ribbon, Design Tab, Mark as Date Table).Currently I am working on a project for an easy to update calendar table, and if you do not already have such a calendar table resource, would you be interested in having one?
The forum ‘Power Pivot’ is closed to new topics and replies.