Fiscal Start of Year = Today

[ronkochanowski]

Good morning (or Good day!),

From the previous issue, you found that I have been standardizing my date tables across all reports in Power BI. Yesterday, I got everything updated and appearing to function as desired.

Today, the start of the new fiscal year, all of my prior week measures went to "0". Inspecting a variable inside of one of the measures, PreviousFiscalYearWeek, should have provided the resulting value of 6243, today's "Fiscal YearWeekNumber" - 1. Instead, the result was -1.

This was odd, zero minus one equals -1. Why would this sequential value become zero? So I looked at the Date table. No new entry has been created for today! Here are the first few lines of the table definition:

Date =

-- -- Configuration

---

VAR TodayReference = TODAY () -- Change this if you need to use another date as a reference "current" day VAR FirstYear = 2012 VAR LastYear = YEAR ( TodayReference ) VAR FiscalCalendarFirstMonth = 9 -- For Fiscal 52-53 weeks (start depends on rules) and Gregorian (starts on the first of the month) VAR FirstDayOfWeek = 1 -- Use: 0 - Sunday, 1 - Monday, 2 - Tuesday, ... 5 - Friday, 6 - Saturday VAR TypeStartFiscalYear = 1 -- Fiscal year as Calendar Year of : 0 - First day of fiscal year, 1 - Last day of fiscal year VAR IsoCountryHolidays = "US" -- Use only supported ISO countries or "" for no holidays VAR WeeklyType = "Last" -- Use: "Nearest" or "Last" VAR QuarterWeekType = "445" -- Supports only "445", "454", and "544" VAR CalendarRange = "FiscalGregorian" -- Supports "Calendar", "FiscalGregorian", "FiscalWeekly"

Should this not generate the row for today at midnight each day?

[marcosqlbi]

I'm not sure I understand the issue. Can you clarify what is the current and expected result?

[datadu-de]

Are you processing the date table every night? It will only generate new rows when the table is processed

[ronkochanowski]

@marcosqlbi The most recent date in the date table is August 31, 2020. The row in the date table for Sept 1, 2020 has not been created. I'm wondering at what point the entry for September 1 is generated?

[ronkochanowski]

@datadu-de The report is refreshed several times during the day, beginning at 3am.

Just verified that the refresh occurred...

[ronkochanowski]

A screen shot of the date table...

[datadu-de]

@ronkochanowski

Just verified that the refresh occurred...

That’s really strange then. I’m not sure about the behavior regarding time zones.

If for example you’re in gmt+0 but the server is gmt+6 (US east) at 3am your time the refresh occurs, but according to the server it’s 9pm “the day before”, so the Date table is ending “yesterday”.

Just a thought. Not sure if that’s the issue here but it could be (part of) the cause

[ronkochanowski]

@datadu-de @marcosqlbi Created a new measure: CurrentDay = Today() Dropped this onto a Card visual: So it appears that the engine is reading in local/computer time...

[datadu-de]

@ronkochanowski My theory is dead then 😉 Maybe Marco has another idea

[ronkochanowski]

@marcosqlbi The temporary fix was to change VAR CalendarRange = "FiscalGregorian" to "Calendar".

Doing this generated dates through 2020-12-31. Since the LastYear reference will update to the new calendar year on 2021-01-01, the following year will be built accordingly...or it should build as designed.

[marcosqlbi]

The template works by design. The LastYear variable should be set to the last year you want to include. If you set "CalendarRange" to "FiscalGregorian", the LastYear corresponds to the last year in that calendar. According to the parameters of your date table, you are saying you want to include dates until the end of the fiscal year 2020, which ends on 8/31/2020. If you want to always include the year that includes the TodayReference, you should invert FiscalCalendarFirstMonth and LastYear definition writing this:

VAR FiscalCalendarFirstMonth = 9 -- For Fiscal 52-53 weeks (start depends on rules) and Gregorian (starts on the first of the month) VAR LastYear = YEAR ( TodayReference ) + (MONTH ( TodayReference) >= FiscalCalendarFirstMonth)

This way you get the result you expect. Because the variables are the parameters of the Date table, I would not change the current template. Thanks!