Hi, I have the following measure
DCM Month to Month Variance % = DIVIDE( [DCM], CALCULATE( [DCM], DATEADD('Calendar'[Date], -1, MONTH ) ), 0 ) -1
This works great for showing the month to month variance of the DCM measure. What I want to do is to be able to drill down through Month > Quarter > Year and have the measure adjust to that time period accordingly.
What I am trying to avoid is having 3 charts displaying this i.e. one of month variance, one for quarterly variance etc.
Is this possible to do?
I've finially have a quiet day to come back to this and between what you both posted as well as info at the link below, I was able to get this working.......almost
One issue I have having is in relation to the variance calculation for Years. For some reason, I am getting a value of 19% where I should only be getting a value of 7% when I look at the variance between 2021 & 2020 and I can't understand why. All other year, month & quarter calculations work perfectly.
I would appreciate if you could take a quick look at see if you can spot what the issue is.
My measure is as follows
Variance % Change - DCM = VAR MonthChange = DIVIDE( [DCM], CALCULATE( [DCM], DATEADD('Calendar'[Date], -1, MONTH ) ), 0 ) -1 VAR QuarterChange = DIVIDE( [DCM], CALCULATE( [DCM], DATEADD('Calendar'[Date], -1, QUARTER ) ), 0 ) -1 VAR YearChange = DIVIDE( [DCM], CALCULATE( [DCM], DATEADD('Calendar'[Date], -1, YEAR ) ), 0 ) -1 RETURN SWITCH(TRUE() , ISINSCOPE('Calendar'[Month]), MonthChange , ISINSCOPE('Calendar'[Qtr & Year]), QuarterChange , ISINSCOPE('Calendar'[Year]), YearChange )
The raw numbers per the DCM measure are below. Scratch that, forum wont let me post tables no matter what I do. Apologies, this is in text
Year - DCM - Variance - Variance %
2019 - 2761 - null - null
2020 - 3066 - 305 - 11%
2021 - 3292 - 226 - 7%
Month MPD Var Var %
Feb 2664 115 5%
Mar 2732 68 3%
Apr 2816 84 3%
May 2547 -269 -10%
Jun 2630 83 3%
Jul 2849 219 8%
Aug 2994 145 5%
Sep 2795 -199 -7%
Oct 2688 -107 -4%
Nov 3045 357 13%
Dec 2844 -201 -7%
Jan 2792 -52 -2%
Feb 2864 72 3%
Mar 2664 -200 -7%
Apr 2814 150 6%
May 2677 -137 -5%
Jun 3106 429 16%
Jul 3129 23 1%
Aug 3635 506 16%
Sep 3173 -462 -13%
Oct 3273 100 3%
Nov 3419 146 4%
Dec 3280 -139 -4%
Jan 3071 -209 -6%
Feb 3341 270 9%
Mar 3790 449 13%
It looks like the problem might relate to 2021 being a partial year, depending how your Calendar table is set up.
Does your Calendar include all of 2021 or just up to March?
Best practice would be to include complete years in your Calendar table, in this case up to the end of 2021.
This is because the DATEADD function will shift only the dates that actually exist in the Calendar table.
For example, if your Calendar table ends at March 2021, this expression:
CALCULATE( [DCM], DATEADD('Calendar'[Date], -1, YEAR ) )
when filtered on Year = 2021, would return just Jan-March 2020.
If possible, could you share a sanitised PBIX showing the issue?
That make sense. Unfortunately I cant share the pbix file.
I think I will set it to just show month & quarter only as I'd rather not modify the date table if possible.
Hmm, actually, will this issue also present due to incomplete months/incomplete quarters? If it will, then I think I may need to update the calendar and just fix all the issues that will cause
I forgot to ask earlier, but just checking that your date table has contiguous dates?
And as you've described, for the month/quarter/year time intelligence functions to work, you need complete months/quarters/years.
By the way,
This article shows some techniques for handling situations where date tables extend beyond the date range of the data.
This is an interesting requirement.
I would describe it as a "dynamic prior period" calculation, where a single measure needs to detect the type of period selected (month/quarter/year) and shift the date filter to the prior period of that type.
Here is a measure I've used in the past. I haven't looked at it recently and it could well be improved upon. I'm sure I have seen some other similar solutions out there as well.
Prerequisites for this measure to work:
Using [DCM] as your base measure, it would be:
DCM Prior Period Dynamic = VAR DateCount = COUNTROWS ( 'Calendar' ) VAR MinDate = MIN ( 'Calendar'[Date] ) // Year variables VAR DateCountParallelYear = CALCULATE ( COUNTROWS ( 'Calendar' ), PARALLELPERIOD ( 'Calendar'[Date], 0, YEAR ) ) VAR YearCount = DISTINCTCOUNT ( 'Calendar'[Calendar Year] ) // Month variables VAR DateCountParallelMonth = CALCULATE ( COUNTROWS ( 'Calendar' ), PARALLELPERIOD ( 'Calendar'[Date], 0, MONTH ) ) VAR MonthCount = DISTINCTCOUNT ( 'Calendar'[Calendar Month Year] ) RETURN SWITCH ( TRUE (), DateCount = DateCountParallelYear, CALCULATE ( [DCM], DATESINPERIOD ( 'Calendar'[Date], MinDate - 1, - YearCount, YEAR ) ), DateCount = DateCountParallelMonth, CALCULATE ( [DCM], DATESINPERIOD ( 'Calendar'[Date], MinDate - 1, - MonthCount, MONTH ) ), // Default to date count CALCULATE ( [DCM], DATESINPERIOD ( 'Calendar'[Date], MinDate - 1, - DateCount, DAY ) ) )
The logic of this measure is:
I might also suggest a tweak to your variance % measure to blank out the result when either current or previous value is blank:
DCM Dyanamic Variance % = VAR Current = [DCM] VAR Previous = [DCM Prior Period Dynamic] RETURN IF ( NOT ISBLANK ( Current ) && NOT ISBLANK ( Previous ) DIVIDE ( Current, Previous, 0 ) - 1 )
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Mark your calendars and join us for our next Power BI Dev Camp!