cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Prior Period dependent on Month Slicers

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!

1 ACCEPTED SOLUTION
Super User

Something like this
you should have a date table

``````Measure =
VAR CounMonth = DISTINCTCOUNT('Calendar'[Month Number])
RETURN
6 REPLIES 6
Super User

Hi,

PBI file attached.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper II

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

Super User

Something like this
you should have a date table

``````Measure =
VAR CounMonth = DISTINCTCOUNT('Calendar'[Month Number])
RETURN
Helper II

This is perfect. Accepting as solution. Many thanks

Super User

@AndySmith simply do this measure:

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

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.