Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    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

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

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

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    5 years ago

    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.