Problems with time intelligence functions and fragmentators after re-generating MasterCalendar table

[DominikZet]

I have problems with automatically created date / date hierarchy table after re-generating Master Calendar table in Power BI desktop application:

• when You change the time scope in Master Calendar table,
• then the Master Calendar table and automatic date hierarchy are re-created,
• then
  • time intelligence functions stop to work correctly.
  • fragmentators with data from automatic date hierarchy stop to show correctly

Here is the detailed case:

Initially - I create the master calendar table named 99_Kalendarz - with the ADDCOLUMNS or GENERATE/RETURN ROW for period: from “1 January 2015” to “31 December 2018”.

99_Kalendarz =
ADDCOLUMNS (
CALENDAR (DATE(2015;1;1); DATE(2018;12;31)) ;
"DataSprz"; FORMAT ( [Date]; "YYYY-MM-DD" );
"Rok"; YEAR ( [Date] );
"Mies"; FORMAT ( [Date]; "MM" );
"RokMies"; FORMAT ( [Date]; "YYYY/MM" );
"DzTygNr"; WEEKDAY ( [Date]; 2 );
"DzTyg"; FORMAT ( [Date]; "ddd" );
"Kw"; "Q" & FORMAT ( [Date]; "Q" );
"RokKw"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )
)

 In both cases Master Calendar table 99_Kalendarz contains the main field [Date] with the complete set of dates in the given period. The [Date] field has datatype Date/Hour and for this field the date hierarchy Year/Quarter/Month/Day is created automatically.

Later You refer to those fields like that:

‘99_Kalendarz[Date].[Date]      – main “Date” field

‘99_Kalendarz[Date].[Rok]        – field „Year” in automatically created hierarchy

‘99_Kalendarz[Date].[Kwartał]  – field „Quarter” in automatically created hierarchy

‘99_Kalendarz[Date].[Miesiąc]  – field „Month” in automatically created hierarchy

‘99_Kalendarz[Date].[Dzień]     – field „Day” in automatically created hierarchy

Then I join the master calendar table to the sales data table and create some time intelligence measures. In time intelligence measures I relate to the master calendar table like this: provide the notation of the field [Date] as a “hierarchical” field in master calendar table: ‘99_Kalendarz’[Date].[Date] as mentioned above:

Sp_WartVKP_LY = CALCULATE( '01_Sprzedaz'[Sp_WartVKP]; SAMEPERIODLASTYEAR( '99_Kalendarz'[Date].[Date] ) )

Time intelligence measures work as expected.

I can also create Fragmentators based on the master calendar table filed ‘99_Kalendarz”[Date].

I have to instruct Fragmentator visualization which part of [Date] field should it use.

[Date].[Date] field or some of automatically created date hierarchy field.

Fragmentators work properly and they show the chosen subpart of [Date] field: Date itself or Year/Quarter/Month/Day (from hierarchy) 

And here comes the problem:

I add new year 2019 to the Master Calendar table ‘99_Kalendarz’

I correct the data for CALENDAR in the table definition:

Then the Master Calendar table is re-generated and also the date hierarchy is probably re-generated automatically

But: After re-generation - the automatic date hierarchy is not visible on the Fields pane.

After this procedure:

• Time intelligence measures stop to work properly.

The notation check does not show any errors - but SAMEPERIODLASTYEAR calculates the “present period” instead of “same period last year”

• Fragmentators which used fields from date hierarchy start to show the basic “Date” field instead of “Year” or “Month” from date hierarchy – probably due to re-generation of automatic date hierarchy

Can You suggest - is it a bug or how can I add the new period (new year) to the master calendar without destroying the Data Model ?

[v-qiuyu-msft]

Hi @DominikZet,

Would you please share a pbix file with us? If it is, please upload it to your OneDirve and send the share link to me via private message. 

Best Regards,
Qiuyun Yu