Date Table: To use start of and end of year dates or not

[akhaliq7]

If I am creating a date table does the table contain dates from the beginning of the year to the end of the year even if those dates don't exist in the fact table.

e.g.

fact table date range: 02/02/2022 - 01/06/2023

date table range: 01/01/2022 - 31/12/2023

this is recommended by most power bi instructors and tutors online. But when using date related dax functions the date table becomes disfunctional. An example of a date function when this happens:

Last 12 Months V1 =
CALCULATE ( 
      [Total Sales],
      DATESINPERIOD ( 'Date'[Date],          -- returns period from date column
                      MAX ( 'Date'[Date] ),  -- starting from MAX date
                      -12,                   -- shifting it back 12 intervals
                      MONTH                  -- each interval being a month
      )
)

Am I missing something? or do you create a copy date table for dax functions? 

[tamerj1 · Accepted solution]

Hi @akhaliq7 
Please try

Last 12 Months V1 =
IF (
    NOT ISEMPTY ( Sales ),
    CALCULATE (
        [Total Sales],
        DATESINPERIOD (
            'Date'[Date],
            -- returns period from date column
            MAX ( 'Date'[Date] ),
            -- starting from MAX date
            -12,
            -- shifting it back 12 intervals
            MONTH -- each interval being a month
        )
    )
)

[akhaliq7]

Thanks, what I don't get is why have the date table range to the end of the year if work arounds are required in dax. why not just use the max date in sales as the last date in the date table.

The reason I don't is because having watched many courses this is recommended to have the last date in the date table to be the end of year for the max date in the sales table so if the max date in the sales table is 01/06/2023 then the last date in the date table is recommended to be 31/12/2023.

[tamerj1]

@akhaliq7 

If there is no special requirement then you don't have to. Just use min, max. 

[akhaliq7]

Ok thanks for your help

[akhaliq7]

Yes but I am asking if a date table range is 01/01/2022 - 31/12/2023 and the fact table range is 02/02/2022 - 01/06/2023 and you use MAX('Date'[Date]) then you get the valuw 31/12/2023 then when you use this for a time intelligence function such as dates in period like I have done in the function in my question then you get all sales amount from the date range 01/01/2023 - 31/12/2023 but that is not what I want I want the date range 01/06/2022 to 01/06/2023 so what I don't get is why use max 'date'[date] and why have dates to the end of the year if it incorrectly affects your time intelligence functions

[some_bih]

Hi @akhaliq7 Date table in Power BI is fundamental table if you need to do time intelligence calculation. I would say, there is nothing disfunctional with Date table in Power BI. 

Did you mark your Date table as Date table? Sometimes I forget to do it 🙂

[akhaliq7]

Yes I mark my date tables as a date table