Create Measure - Period On Period % Diff, Based On User's Input In Built-In Relative Date Slicer For

[CALCULATEpizza]

Hi everyone!

Would appreciate any help on the following requirements:

I'd like to create a measure for:

- Period On Period % Diff

- Based On User's Input In Built-In Relative Date Slicer

- For Matrix Visual With Date Dimension In Columns (Year, Month, Week)

The month column in matrix should also show month names (Jan, Feb, Mar, etc...)

Here's the pbix file

https://wetransfer.com/downloads/c4520acc0588798246d10c01d429de6420220330165102/76b28ehttps://wetran...

The dataset is shown in the picture below. 

The expected results are below:

(Disclaimer - I had to create 2 separate measures in order to show the expected results. Whereas what I'd like is to have a single measure that is able to display the output show by these 2 measures. Extending further, it should also show the correct output when, for example, "last 3 days" is selected in slicer, but matrix column is on weekly level. Or "last 3 weeks" selected in slicer, but matrix column is on monthly level.)

When selecting "last 2 months" on slicer, and matrix column is on monthly level:

When selecting "last 3 months" on slicer, and matrix column is on monthly level:

When selecting "last 2 weeks" on slicer, and matrix column is on weekly level:

 When selecting "last 3 weeks" on slicer, and matrix column is on weekly level:

[Anonymous]

Hi @CALCULATEpizza ,

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR _sum =
    CALCULATE (
        SUM ( 'Sales Data'[QTY Sold] ),
        FILTER (
            ALL ( 'Date Dimension' ),
            'Date Dimension'[Week] = SELECTEDVALUE ( 'Date Dimension'[Week] )
        )
    )
RETURN
    DIVIDE ( SUM ( 'Sales Data'[QTY Sold] ), _sum )

If I have misunderstood your meaning, please provide your desired output with a screenshot.

How to Get Your Question Answered Quickly 

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CALCULATEpizza]

Hi @Anonymous, 

Thanks so much for your response, unfortunate the measure doesn't provide the output I'm looking for. I've updated my original post with the expected results.

I have also reuploaded the pbix file. In this update:

- added 2 measures to demonstrate what the output should be

[MFelix · Accepted solution]

Hi @CALCULATEpizza ,

Believe that the main issue you have here is the fact that you cannot know how the relative slicer selection is made, if it's on weeks, month or days.

What you can do keeping the two measures you have is to create a measure that gets the context of the matrix, and returns the correct measure:

Switch bewteen = if(ISINSCOPE('Date Dimension'[Week]), [WEEK ON WEEK % DIFFERENCE], [MONTH ON MONTH % DIFFERENCE])

Has you can see when you drill up or down the calculations changes:

[CALCULATEpizza]

@MFelix Thanks so much! This solves exactly what I'm trying to accomplish. Appreciate your time looking into this.

[MFelix]

Hi @CALCULATEpizza ,

Add and index colum for the week:

INT( ROUNDDOWN( ( DATEDIFF( [Date], TODAY(), DAY) * -1 + -1 * IF( WEEKDAY( TODAY() ) + 1 <= 6, WEEKDAY( TODAY() ) + 1, WEEKDAY( TODAY() ) + 1 - 7 )) / 7, 0 ) )

Then try the following measure:

Measure = var WeekSelection = 
 MAX('Date Dimension'[weekindex]) - 1 

var _sum = CALCULATE(SUM('Sales Data'[QTY Sold]), FILTER(ALL('Date Dimension'),'Date Dimension'[weekindex] = WeekSelection))
return
DIVIDE(SUM('Sales Data'[QTY Sold]), _sum)

[MFelix]

Hi @CALCULATEpizza 

You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[CALCULATEpizza]

@MFelix thanks so much for the tip!