Forum Discussion

N_R_000's avatar
N_R_000
Helper I
5 years ago

DAX Dynamic Date Table based on Selection

I need to build a dynamic date table which holds dates for the pevious 3 calendar months based on a month selected by a slicer. 

 

So if the user selects June 2021, I need to build a list of dates from 1st March 2021 - 31st May 2021

 

I've been using this to test the theory, which returns the dates I need:

Dim3Months = DATESINPERIOD(dim_Date[Date], DATE(2021, 03, 01), 3, MONTH)
 
but I can't get the correct syntax to replace the DATE(2021, 03, 01) part. If I use the slicer selected date [Report Run Date] shown in blue below, the date table starts at 1900 
 

 

Any pointers on what I can do to overcome?  

 

 

Need Help

4 Replies

    • N_R_000's avatar
      N_R_000
      Helper I
      5 years ago

      Unfortunately, I can't as it's work related. But thi sproblem only affects one table (dim_Date) which contains every date from 1901 to 2099 (imported from data warehouse)

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    Hi N_R_000 

     

    Download the attached pbix file to see whether this meets your need.

     

    Since you want the slicer to filter the dates in previous 3 months, the dates for the table and for the slicer should come from different disconnected tables so that they won't filter each other based on the relationship.

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.