Forum Discussion
Issue with Constant date value in slicer
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
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?)
2 Replies
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?)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
