Forum Discussion

Caldowd98's avatar
Caldowd98
Helper I
3 years ago
Solved

Date Table

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 r...
Need Help
Tips and Tricks
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

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

     

PaulDBrown's avatar
PaulDBrown
Community Champion
3 years ago

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's avatar
Caldowd98
Helper I
3 years ago

Thanks Paul - perfect 🙂