Date Table

[Caldowd98]

Hi community

I have a dataset where the last entry against a date is 01.09.2022. My current date table (below) returns a date table with the last date 31.12.2022.

How would i change this so it returns the maximum date in the dataset and not the final day of the year of this date ?

Date Table =

ADDCOLUMNS(

CALENDARAUTO(),

"YEAR", YEAR([Date]),

"Month", FORMAT([Date], "mmmm"),

"MONTH NUMBER", MONTH([Date]))

Thanks!! 🙂

[PaulDBrown · Accepted solution]

Let's say the date field in your fact table is called 'FactTable'[Date]

You can limit the start and end date in the calendar table by using:

Date Table =
VAR _MinDate =
    MIN ( FactTable[Date] )
VAR _MaxDate =
    MAX ( FactTable[Date] )
RETURN
    ADDCOLUMNS (
        CALENDAR ( _MinDate, _MaxDate ),
        "YEAR", YEAR ( [Date] ),
        "Month", FORMAT ( [Date], "mmmm" ),
        "MONTH NUMBER", MONTH ( [Date] )
    )

[Caldowd98]

Thanks Paul - perfect 🙂

[mangaus1111]

Hi @Caldowd98 ,

I don´t reccomend you to limit the rows of your calendar table to the last date of your fact table. You can have problems with time intelligence functions.

See here how a calendar table must be written:

https://community.powerbi.com/t5/Community-Blog/Time-Intelligence-Calendar-Table/ba-p/1000565

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.