Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Make Calendar dates in sync with model dates

 

Hi all,

In my model i have data only for october 2021 but when i take dates from calendar table it shows me all year and months.I want to see the year and months present in my model.

how can i make the calendar table dates in sync with my model.I generated this calendar table through M query and have a relationship with two of the tables in model.         Greg_Deckler amitchandak 

         Thanks,

Nj                                

 

 

 

   

Need Help

5 Replies

  • danextian's avatar
    danextian
    Super User
    4 years ago

    Hi Anonymous,

    You can create a calendar in DAX that is based on the earliest and latest date in your other tables.

    CALENDAR =
CALENDAR ( MIN ( Table[Date] ), MAX ( Table.Date ) )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi danextian I know I can create this but i want to know how can i do it in Date table itself

      • danextian's avatar
        danextian
        Super User
        4 years ago

        Hi Anonymous ,

         

        If you want to edit  your Dates table, you have two options:
        Power Query - Filter the date column dynamically so based on the earliest and latest date in your Fact table with these filter criteria - each [Date] >=List.Min ( Fact[Date] ) and [Date] <=List.Max ( Fact[Date] ). How fast this approach depends on how big and fast your data source connection is.

        DAX  - create a calculated column to identify whether a date in the Dates table is within the min and max dates of the Fact Table.

        Indicator =
Dates[Date] >= MIN ( Fact[Date] )
    && Dates[Date] <= MAX ( Fact[Date] )

        This will return a True/False value. You can use this in the filter pane to filter the whole report or just  specific pages/visuals.

  • PC2790's avatar
    PC2790
    Community Champion
    4 years ago

    Hey Anonymous ,

     

    If you are looking to do it Power Query itself,

    You can use:

    StartDate = List.Min(YourTable[DateColumn]),
    EndDate = List.Max(YourTable[DateColumn])

     

    Refer this to have step by step illustration of the same.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi PC2790 

      I have multiple tables, i cant create date table based on a single table from my model.I need to use a dim Date table which I am using but the Date table is not in sync with dates available in Model