cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Prodigy

## 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
)
)
)
Post Prodigy

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.

Post Prodigy

Post Prodigy

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

Super User

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 🙂

Proud to be a Super User!

Post Prodigy

Yes I mark my date tables as a date table

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors