Forum Discussion
Solved
Date formula correct - Range of dates wrong
Hello everyone
I have pasted the code below to get a data table for a calendar in my power bi workbook. The code is working correctly but the range of dates I'm getting is 2015 to 2017. Where am I going to have to input the right range in this code? Much appreciated!
GQR Calendar =
--Inputs--
VAR WeekStartsOn = "Mon"
VAR FiscalStartMonth = 4
--NOTE: Calendar week starts from Monday
--Calculation--
RETURN
ADDCOLUMNS (
CALENDARAUTO ( FiscalStartMonth - 1 ),
"MIndex", MONTH ( [Date] ),
"FiscalMIndex", MONTH ( EDATE ( [Date], - FiscalStartMonth + 1 ) ),
"CalMonth", FORMAT ( [Date], "mmm" ),
"CalQtr", "Q"
& CEILING ( MONTH ( [Date] ), FiscalStartMonth - 1 ) / ( FiscalStartMonth - 1 ),
"CalYear", YEAR ( [Date] ),
"Fiscal Week",
VAR FiscalFirstDay =
IF (
MONTH ( [Date] ) < FiscalStartMonth,
DATE ( YEAR ( [Date] ) - 1, FiscalStartMonth, 1 ),
DATE ( YEAR ( [Date] ), FiscalStartMonth, 1 )
)
VAR FilteredTableCount =
COUNTROWS (
FILTER (
SELECTCOLUMNS ( GENERATESERIES ( FiscalFirstDay, [Date] ), "Dates", [Value] ),
FORMAT ( [Dates], "ddd" ) = WeekStartsOn
)
)
VAR WeekNos =
IF (
FORMAT ( FiscalFirstDay, "ddd" ) <> WeekStartsOn,
FilteredTableCount + 1,
FilteredTableCount
)
RETURN
"Week " & WeekNos,
"Fiscal Qtr", "Q"
& CEILING ( MONTH ( EDATE ( [Date], - FiscalStartMonth + 1 ) ), 3 ) / 3,
"Fiscal Year",
VAR CY =
RIGHT ( YEAR ( [Date] ), 2 )
VAR NY =
RIGHT ( YEAR ( [Date] ) + 1, 2 )
VAR PY =
RIGHT ( YEAR ( [Date] ) - 1, 2 )
VAR FinYear =
IF ( MONTH ( [Date] ) > ( FiscalStartMonth - 1 ), CY & "-" & NY, PY & "-" & CY )
RETURN
FinYear,
"CalWeekNo", WEEKNUM ( [Date], 2 ),
"Weekend/Working", IF ( WEEKDAY ( [Date], 2 ) > 5, "Weekend", "Working" ),
"Day", FORMAT ( [Date], "ddd" ),
"CustomDate", FORMAT ( [Date], "d/mm" )
)
Solved: I had to change the VAR CY = element from a 2 to a 20 to get to year 2023
2 Replies
Solved: I had to change the VAR CY = element from a 2 to a 20 to get to year 2023
Hi powerbienthuzi ,
Glad to hear that your issue has been resolved by yourself !
Could you please Accept your post as the solution to make the thread closed? Thanks in advanced😀
Best Regards,
Eyelyn Qin
