Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Issue with Dax Code to create custom Slicer getting error

This is the code:

I am getting the following error "The start date or end date in Calendar function can not be Blank value"

I followed the directions in an example and I cannot figure out what I am missing.

 

MTD/QTD/YTD Selection = 
VAR TodayDate = TODAY()
VAR YearStart = CALCULATE(STARTOFYEAR(SL_Data[Month Year]), YEAR(SL_Data[Month Year]) = YEAR(TodayDate) )
VAR QuarterStart = CALCULATE(STARTOFQUARTER(SL_Data[Month Year]), YEAR(SL_Data[Month Year]) = YEAR(TodayDate), QUARTER(SL_Data[Month Year]) = QUARTER(TodayDate) )
VAR MonthStart = CALCULATE(STARTOFMONTH(SL_Data[Month Year]), YEAR(SL_Data[Month Year]) = YEAR(TodayDate), MONTH(SL_Data[Month Year]) = MONTH(TodayDate) )
VAR Result = 
UNION (
    ADDCOLUMNS (
        CALENDAR ( YearStart, TodayDate ),
        "Selection", "YTD",
        "Sort", 1
    ),
    ADDCOLUMNS (
        CALENDAR ( QuarterStart, TodayDate ),
        "Selection", "QTD",
        "Sort", 2
    ),
    ADDCOLUMNS (
        CALENDAR ( MonthStart, TodayDate ),
        "Selection", "MTD",
        "Sort", 3
    )
)
RETURN
Result

 

  • Hi,

     

    Without seeing The data my speculation is that  your Column: SL_Data[Month Year]. Is not date/time Column. Because of this STARTOFYEAR returns blank. You yhen refer to this variable on your CALENDAR function resulting on The error.

     

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

1 Reply

  • ValtteriN's avatar
    ValtteriN
    Community Champion

    Hi,

     

    Without seeing The data my speculation is that  your Column: SL_Data[Month Year]. Is not date/time Column. Because of this STARTOFYEAR returns blank. You yhen refer to this variable on your CALENDAR function resulting on The error.

     

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/