Prior Period dependent on Month Slicers

[AndySmith]

Hi

I have a sales table and a Date Table. I am using a Month Year Slicer.

Would like to calculate the sum of sales for the prior period based on this slicer. So if I have 1 month selected - I want to see the previous month. If I have 2 months selected, I want to sum sales of the previous 2 months. 3 months selected? Guess what? Yep - I want to see sales for the 3 months prior to the selection.

Example: If Sept, Oct, Nov selected in slicer, I want sum of Jun, July, August

Thanks!

[Ashish_Mathur]

Hi,

PBI file attached.

[parry2k]

@AndySmith the solution @Ahmedx provided will work - I missed the dynamic part. Cheers!

[AndySmith]

Thanks!

But I need it to be dynamic - there might be 6 months selected, so I want to see the 6 months prior, or 15 months, so I want to see the 15 months prior

[Ahmedx · Accepted solution]

Something like this
you should have a date table

Measure = 
VAR CounMonth = DISTINCTCOUNT('Calendar'[Month Number])
RETURN
CALCULATE(SUM(yourtable[Sales]), DATEADD('Calendar'[Date],-CounMonth,MONTH))

[AndySmith]

This is perfect. Accepting as solution. Many thanks

[parry2k]

@AndySmith simply do this measure:

Previous Period = 
CALCULATE ( 
   [Your Measure],
   DATEADD ( 'Date Table'[Date], -3, MONTH )
)

Learn more about DATEADD function here:

In depth review of most powerful and flexible DATEADD Time Intelligence function - Part 3 - YouTube 

How PREVIOUS/NEXT (DAY/MONTH/QTR/YEAR) Time Intelligence functions are different from DATEADD-Part 4...