Variance measure, how to make it work with date hierarchy e.g. Month / Quarter / Year

[DaveCor]

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?

[DaveCor]

Hi @OwenAuger & @amitchandak Thank you for your assistance with this. Its appreciated, most especially because it taught me a lot. 

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

Highlighting Below Avg Sales per Hierarchy Level with SWITCH() and ISINSCOPE() DAX Functions in Powe...

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 %
Jan 2549
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%

[DaveCor]

Can anyone understand what is going wrong here? It has me baffled

[OwenAuger]

Hi @DaveCor 

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?

Regards,

Owen

[DaveCor]

Hi @OwenAuger 

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

[OwenAuger]

No worries.

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.

[OwenAuger]

@DaveCor 

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:

• Your 'Calendar' table must include columns 'Calendar'[Calendar Year] and 'Calendar'[Calendar Month Year] or similar, to allow years and months to be identified.
• It is assumed that a contiguous date filter is applied whenever the measure is used, in order for the date counts to make sense.

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:

1. Detect whether the filtered date range corresponds to a sequence of complete calendar years (could be 1 or more). If so, shift the date filter to the same number of years immediately preceding the filtered date range.
2. Otherwise, detect whether the filtered date range corresponds to a sequence of complete calendar months (could be 1 or more). If so, shift the date filter to the same number of years immediately preceding the filtered date range. Note that this will handle calendar quarters as well as other sequences of complete months.
3. Otherwise, simply use the date count to determine the size of the currently filtered period (e.g. 77 days), and shift to the previous period of the same number of days.

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
    )

Regards,

Owen

[amitchandak]

@DaveCor , I think you should able to do that with isinscope

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/