topic Re: Date Table: To use start of and end of year dates or not in DAX Commands and Tips

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

akhaliq7 — Thu, 01 Jun 2023 12:43:06 GMT

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?

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

some_bih — Thu, 01 Jun 2023 14:18:02 GMT

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 🙂

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

akhaliq7 — Thu, 01 Jun 2023 14:26:48 GMT

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

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

tamerj1 — Thu, 01 Jun 2023 14:27:54 GMT

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

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

akhaliq7 — Thu, 01 Jun 2023 14:28:20 GMT

Yes I mark my date tables as a date table

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

akhaliq7 — Thu, 01 Jun 2023 14:36:37 GMT

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.

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

tamerj1 — Thu, 01 Jun 2023 14:42:00 GMT

akhaliq7

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

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

akhaliq7 — Thu, 01 Jun 2023 15:04:03 GMT

Ok thanks for your help