Forum Discussion

MSuser5's avatar
MSuser5
Helper III
2 years ago
Solved

create calendar based on source date

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
Need Help

6 Replies

  • Idrissshatila's avatar
    Idrissshatila
    Super User
    2 years ago

    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's avatar
      MSuser5
      Helper III
      2 years ago

      Idrissshatila ,

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

      FYI,

       

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

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