Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am adding a Fiscal week column to a date table. (Begins Aprils)
The formula is quite extensive and is as below.
Week No =
--Inputs--
VAR WeekStartsOn = "Sun"
VAR FiscalStartMonth = 4
--Calculation--
VAR FiscalFirstDay =
IF(
MONTH('Calendar_Entry_Date'[Date]) < FiscalStartMonth,
DATE(
YEAR('Calendar_Entry_Date'[Date])-1,
FiscalStartMonth,
1
),
DATE(
YEAR('Calendar_Entry_Date'[Date]),
FiscalStartMonth,
1
)
)
VAR FilteredTableCount =
COUNTROWS(
FILTER(
SELECTCOLUMNS(
GENERATESERIES(
FiscalFirstDay,
'Calendar_Entry_Date'[Date]
),
"Dates",
[Value]
),
FORMAT([Dates],"ddd") = WeekStartsOn
)
)
VAR WeekNos =
IF(
FORMAT(FiscalFirstDay,"ddd") <> WeekStartsOn,
FilteredTableCount + 1,
//1 + WEEKNUM ( 'Calendar'[Date] )-WEEKNUM( STARTOFMONTH ('Calendar'[Date])),
//WEEKNUM([Date])
FilteredTableCount
)
RETURN
WeekNos
The issue is I get the error.
The following syntax error occurred during parsing: Invalid token, Line 16, Offset 3, .
What would be issue?
Solved! Go to Solution.
@Datagulf , if just need Sunday when the year started 1-April
New COlumn =
var _StYear = date(if(month([Date]) <4, Year([Date]) -1, Year([Date]) ),4,1)
return
_StYear - weekday(_StYear ,1) +1
@Datagulf , if just need Sunday when the year started 1-April
New COlumn =
var _StYear = date(if(month([Date]) <4, Year([Date]) -1, Year([Date]) ),4,1)
return
_StYear - weekday(_StYear ,1) +1