Forum Discussion

Zabeer's avatar
Zabeer
Icon for Helper I rankHelper I
6 years ago

Creating dynamic date periods based on a selected value in slicer

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


 

Need Help

4 Replies

  • VijayP's avatar
    VijayP
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      Zabeer
      Icon for Helper I rankHelper I
      6 years ago

      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's avatar
        VijayP
        Icon for Community Champion rankCommunity Champion
        6 years ago

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

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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