Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

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

AD

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
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:

v-robertq-msft_0-1607395590417.png

 

  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:

v-robertq-msft_1-1607395590596.png

 

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.

View solution in original post

3 REPLIES 3
v-robertq-msft
Community Support
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:

v-robertq-msft_0-1607395590417.png

 

  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:

v-robertq-msft_1-1607395590596.png

 

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
Super User
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""

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.