Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create dynamic table based on dropdown filter choice

Hi, 
I'm trying to create a dynamic table as a data source in my report using CALENDAR() function. I'm using this function: 
 
CALENDAR(DATE(YEAR([start_date]),month([start_date]),day([start_date])),DATE(YEAR([end_date]),month([end_date]),day([end_date])))
 
where, 
[start_date] & [end_date] are measures calculated based on a dropdown filter.
For eg: if dropdown filter selection is "Last Week", then 
[start_date]: 13th July,2020
[end_date]: 19th July,2020
 
Thus, the calendar table should contain all date values between 13th July,2020 and 19th July,2020 in a column. But somehow this calendar table is giving incorrect date range (starting with April everytime)  Could you please help with this?
 
Thanks in advance!!
Need Help

6 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    6 years ago

    Hey Anonymous ,

     

    I guess it's not possible what you are going to achieve. This is because virtual tables (tables based on DAX) will only be calculated if the data model is refreshed, or when a DAX statement is changed.

     

    For this reason, a virtual table will not be recalculated when a user changes the selection of a slicer.

     

    Can you please describe in more detail what you are going to achieve, maybe then we can come up with a different approach.

     

    Regards,

    Tom

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi TomMartens ,

       

      Here are my tables: 

       

      Table1: 

      Period                    Start Date          End_Date

      Last Week          13th July,2020          19th July,2020

      Last Month          1st Jun ,2020          30th Jun ,2020

       

      Table2: KPI table

       

      Date                KPI volume

      1st Jan,2020        xxx

      2nd Jan,2020       xxx

      .

      .

      20th July,2020    xxx

       

      I'm looking to create a line trend graph (date vs kpi) for the date period i choose from the dropdown filter. If the filter choice is "Last Week", then the graph shoud be for 13-19 july. 

       

       

      Is there any other way to do this other than creating a dynamic table?

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    While you can make a dynamic table inside a measure, the Calendar table in your model is not dynamic.  It only refreshes when you refresh the data model (not with slicer selections).  Also, why not use CALENDAR(MIN(Table[DateColumn]), MAX(Table[SameorDifferentDateColumn])) ?

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-lionel-msft 

       

      Thanks for this solution, but my date table (which is used to calculate date measures) is in this format: 

       

      Period                         Start Date            End Date

      Last Week                 13th July,2020      19th July,2020

      Last Month                1st Jun,2020         30th Jun,2020

       

      There is no date column which can be used to create direct relationship between this and the dynamic table.