cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## Create date range using slicer's input

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"

1 ACCEPTED SOLUTION
Community Support

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:

1. 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.

3 REPLIES 3
Community Support

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:

1. 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.

Super User

@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
Not applicable

@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""

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors