Creating dynamic date periods based on a selected value in slicer

[Zabeer]

Hello PowerBI Community.

I would like to create 3 dynamic date periods (dates in the last 12 months, dates between last 12 and 24 months, dates between last 24 and 36 months) based on the date selected in a slicer of the same date table.




So when i select 18/07/2017, i would like to have all dates between 18/07/2016 to 18/07/2017 (last 12 months) in a new table. Similarly, for dates between 18/07/2015 to 18/07/2016 and 18/07/2014 to 18/07/2015.

I tried using DATESINPERIOD to achieve the above result. However, I was unable to provide the selected value from the slicer to <startdate>

Kindly help. Thank you


 

[v-lili6-msft]

hi, @Zabeer 

You could use DATESINPERIOD or DATESBETWEEN Funtion to create the measure like below:

For example:

dates in the last 3 days, dates between last 3 and 6 days, dates between last 6 and 12 days

last 3 days = CALCULATE(SUM('Table'[Value]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,DAY))

last 3 and 6 days = CALCULATE(SUM('Table'[Value]),DATESINPERIOD('Date'[Date],MAX('Date'[Date])-3,-3,DAY))

last 6 and 12 days = CALCULATE(SUM('Table'[Value]),DATESINPERIOD('Date'[Date],MAX('Date'[Date])-6,-6,DAY))

You could adjust it to your requirements.

and here is my simple sample pbix file, please try it.

If not your case, please share your simple sample pbix file and expected output.

Best Regards,

Lin

[VijayP]

Table For Dates = CALENDAR(SELECTEDVALUE(DateTable[Date]),MAX(DateTable[Date]))

Please try this and let me know whether you are able fulfill your requirement

[Zabeer]

Unfortunately, this doesn't work. 
This is the error message :
"The start date or end date in Calendar Function can not be a blank value"

[VijayP]

Create the Slicer of Dates and Select any specific Date to Create the Filter Context with SELECTEDVALUE Function