Create date range using slicer's input

[Anonymous]

Hi community,

I have been stuck at one point  ....solution may or may not be easy here is my Problem.
I have a slicer showing the anyone of the below attribute based on selection

"12 MONTHS ROLLING

3 MONTHS ROLLING

CALENDAR MTD

CALENDAR QTD.."

Now if the the slicer vaue is selected as 3 months rolling then I want to show in another slicer a date range

example: if data period date is 13 Dec...then range should be like this last 3 months period "10/1/2020 - 12/1/2020" 

@Fowmy @mahoneypat @AlB @MFelix @AllisonKennedy @PhilipTreacy 
Regards,

AD

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your description, you want to get the month period rolling sum based on the selection of Slicer of the month period and start the month, you can try my steps:

1. Create two tables, one calculated table :

Date = CALENDARAUTO( )

Click “Enter data” to create another table:

2. Create a measure in the main table:

MONTHS ROLLING =
 var _selectedname=SELECTEDVALUE(Slicer[Name])
 var _selecteddate=SELECTEDVALUE('Date'[Date])
var _startofmonth=DATE(YEAR(_selecteddate),MONTH(_selecteddate),1)
return
SWITCH(
    _selectedname,
    "3 months rolling",CALCULATE(SUM(Sales[Amount]),DATESINPERIOD('Sales'[Date],_startofmonth,-3,MONTH)),
    "6 months rolling",CALCULATE(SUM(Sales[Amount]),DATESINPERIOD('Sales'[Date],_startofmonth,-6,MONTH)),
    "12 months rolling",CALCULATE(SUM(Sales[Amount]),DATESINPERIOD('Sales'[Date],_startofmonth,-12,MONTH)),
    "Please select month rolling")

And create two Slicers(place Slicer[Name] and Date[Date]) and a card chart, then you can get what you want, like this:

You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[amitchandak]

@Anonymous ,Using Date table

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ])-1,-3,MONTH))

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ])-1,-6,MONTH))

Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

@amitchandak Thank you for the input but what i need is date range not the sales vaue sum based on slicer selection..... If in slicer it is selected as 3 months rolling then using a measure I want to create a dynamic date range
for 3 months period it will reflect quarter date range as " "10/1/2020 - 12/1/2020""