Make Calendar dates in sync with model dates

[nj17]

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                                

[PC2790]

Hey @nj17 ,

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.

[nj17]

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

[danextian]

Hi @nj17,

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 ) )

[nj17]

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

[danextian]

Hi @nj17 ,

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.