create calendar based on source date

[MSuser5]

Hi All,

I have source file dates starting from 31/01/2021 to till yesterday ( November 7th 2023) as i have created calendar table based source first date and lastdate.

But while using into calendar date as in slicer it's appearing  till December 2023.

calender = CALENDAR(FIRSTDATE('For Loading'[Day]), LASTDATE('For Loading'[DAY]))

Issue below :

Kindly suggest how to avoid and get till last date of source file.

Thanks,

MS

[Ahmedx]

try this

Calendar = 
VAR BaseCalendar =
    CALENDAR (MIN('For Loading'[DAY]) , MAX('For Loading'[DAY]))
RETURN
    GENERATE (
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR YearDate = YEAR ( BaseDate )
        VAR MonthNumber = MONTH ( BaseDate )
        VAR YearMonthNumber = YearDate * 12 + MonthNumber - 1
        RETURN ROW (
            "Year", YearDate,
            "Month Number", MonthNumber,
            "Month", FORMAT ( BaseDate, "mmmm"),
            "Year Month Number", YearMonthNumber,
            "Year Month", FORMAT ( BaseDate, "mmm yy")
        )
    )

[MSuser5]

Hi @Ahmedx ,

Still no luck FYI,

[Ahmedx]

if it doesn’t work, check your data, it’s all there December

[Ahmedx · Accepted solution]

you disable this option

[Idrissshatila]

@MSuser5 ,

can you try min and max instead of startdate and lastdate.

can you also make sure from the data view that there's no record for december.

[MSuser5]

@Idrissshatila ,

I'm sure there is no data for december  and i have tried for MIN , MAX condition but no luck.

FYI,