search

sql-bi / DaxDateTemplate

Power BI template to create a Date table in DAX
MIT License
267 stars 76 forks source link

FirstDayOfWeek Setting is broken #11

Closed Swazzabalooza closed 5 years ago

Swazzabalooza commented 5 years ago

Hey guys,

Changing the FirstDayOfWeek setting from 0 pulls up the following error:

"CustomFiscalPeriods table does not match FirstDayOfWeek setting"

Marco Russo had provided a workaround to the problem that I've been trying hard to understand and reverse engineer, however I haven't had any luck. This functionality is crucial to my weekly time intelligence analysis.

Thank you

stegerj commented 5 years ago

If restarting from scratch is an option for your project, delete the date table and reinsert a new calculated table copying the code of the date table. Make sure you tweak all the parameters before confirming. This worked for me, be aware all hierarchies get lost. You will also have to fix tables and graphs connected to the previous date table.

marcosqlbi commented 5 years ago

Your workaround seems strange - if there is an issue in DAX it should not be fixed by just deleting and recreating the table with the same DAX code… I will investigate when I have time and I will update you in this thread.

chiaramicheli commented 5 years ago

I had the same problem too. I solved by editing the code when it check the first day:

VAR ReturnType = IF(FirstDayOfWeek = 1; 2 ; 1) VAR CheckFirstDayOfWeek = IF ( WEEKDAY( MINX ( CustomFiscalPeriods; [FirstDayOfYear] );ReturnType) <> (FirstDayOfWeek + 1 ) && WEEKDAY( MINX ( CustomFiscalPeriods; [FirstDayOfYear] );ReturnType) <> (FirstDayOfWeek + 6 );
ERROR ( "CustomFiscalPeriods table does not match FirstDayOfWeek setting" ); TRUE )

It is not the most elegant way, but it's work, waiting for the advice of Marco.

Chiara

cignum commented 5 years ago

Similar issue here, another workaround. Italian fiscal year spans from 1st-Jan to 31-Dec. I also want to set first day of week to monday.

I got the same error and had to force the table CustomFiscalPeriods (pls see below) to a series of periods that do not match the italian fiscal legislation, just to get the query to complete.

VAR CustomFiscalPeriods = DATATABLE ( "Fiscal YearNumber"; INTEGER; "FirstDayOfYear"; DATETIME; "LastDayOfYear"; DATETIME; { { 2016; "2016-01-04"; "2017-01-01" }; -- >>> the first monday of the year is the first fiscal day ... { 2017; "2017-01-02"; "2017-12-31" }; { 2018; "2018-01-01"; "2019-01-06" }; { 2019; "2019-01-07"; "2020-01-05" } } )

marcosqlbi commented 5 years ago

This should be fixed with 1.06 release.