Issue with Constant date value in slicer

[shri2785]

Hi Power BI Gurus, 

I have tried to create a slicer for YTD, QTD, MTD using some links on web. 

Below is the code for the same 

Time_Frame =
VAR TodayDate = Today()
VAR YearStart = Calculate(STARTOFYEAR(Table1 [POSTING_DATE], Year(TodayDate) = Year(TodayDate)))
VAR QuarterStart = Calculate(StartofQuarter(Table 1[POSTING_DATE]), Year(Table 1[POSTING_DATE]) = Year(Todaydate), Quarter(Table 1[POSTING_DATE]) = Quarter(Todaydate))
VAR MonthStart = Calculate(STARTOFMONTH(Table 1[POSTING_DATE]), Year(Table 1[POSTING_DATE]) = Year(Todaydate),Month(Table 1[POSTING_DATE]) = Month(Todaydate))
VAR Result =
Union (
AddColumns (
calendar (YearStart, TodayDate),
"Selection", "YTD"
),
AddColumns (
calendar (QuarterStart, TodayDate),
"Selection", "QTD"
),
AddColumns (
calendar (MonthStart, TodayDate),
"Selection", "MTD"
)
)
RETURN
Result

But this results in an error: 

Only constant date value is allowed as a year end date argument

But when i change the code to below 

Time_Frame =
VAR TodayDate = Today()
VAR YearStart = Calculate(STARTOFYEAR(Table1 [POSTING_DATE], "12-31-2023"))
VAR QuarterStart = Calculate(StartofQuarter(Table 1[POSTING_DATE]), Year(Table 1[POSTING_DATE]) = Year(Todaydate), Quarter(Table 1[POSTING_DATE]) = Quarter(Todaydate))
VAR MonthStart = Calculate(STARTOFMONTH(Table 1[POSTING_DATE]), Year(Table 1[POSTING_DATE]) = Year(Todaydate),Month(Table 1[POSTING_DATE]) = Month(Todaydate))
VAR Result =
Union (
AddColumns (
calendar (YearStart, TodayDate),
"Selection", "YTD"
),
AddColumns (
calendar (QuarterStart, TodayDate),
"Selection", "QTD"
),
AddColumns (
calendar (MonthStart, TodayDate),
"Selection", "MTD"
)
)
RETURN
Result

The error goes away. 

I understand that it needs a constant value but i Also need that to be dynamic in nature. Can anyone help.

Note: the change is on the line 

"VAR YearStart = Calculate(STARTOFYEAR(Table1 [POSTING_DATE], "12-31-2023"))"

Thanks

[Wilson_ · Accepted solution]

Hello shri,

You don't need to include the year in the second parameter in STARTOFYEAR; you can just use "12/31" instead of "12/31/2023". That might solve your issue. I can't quite tell what you're trying to accomplish.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[shri2785]

Hi Wilson, thanks for the response, and it worked for me. I am trying to create a slicer with the list of dates to be passed for measure calculations.

There is one more hurdle i am facing 

we have something called as ficsal_period_start_date in dim_cal

when i try to reference to this it give me circular ref error.

May i know how we can get this done...

Basicall i need 

YTD

QTD

PTD 

as slicer options.Thanks