cancel
Showing results for
Did you mean:
Continued Contributor

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

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?

1 ACCEPTED SOLUTION
Super User

Hi @akhaliq7

``````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
)
)
)``````
7 REPLIES 7
Super User

Hi @akhaliq7

``````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
)
)
)``````
Continued Contributor

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.

Super User

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

Continued Contributor

Continued Contributor

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

Memorable Member

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 🙂

Sarajevo, Bosnia and Herzegovina
Continued Contributor

Yes I mark my date tables as a date table

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors