Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
akhaliq7
Continued Contributor
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
tamerj1
Super User
Super User

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

View solution in original post

7 REPLIES 7
tamerj1
Super User
Super User

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
Continued Contributor
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.

@akhaliq7 

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

akhaliq7
Continued Contributor
Continued Contributor

Ok thanks for your help

akhaliq7
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

some_bih
Super User
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 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






akhaliq7
Continued Contributor
Continued Contributor

Yes I mark my date tables as a date table

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors