Strange behavior with custom weekly calendar

[SalvaC]

Hi all

I'm exploring the new Calendar feature to understand how it works.

I created a custom calendar for the fiscal year based on weeks. Each fiscal year starts in August.

This is the definition of the calendar:

Next, I created a Measure to get the PY value based on this calendar:

Online Sales PY (Fiscal Week) =

    CALCULATE([Sum Online Sales]

                ,SAMEPERIODLASTYEAR('Fiscal Week Calendar')

                )

I added a matrix using the columns defined in the calendar, and these are the results:

As you can see, the results are correct on each level of the calendar hierarchy.

But the cells are empty on weekdays for 23/24 - 1.

This shouldn't be the case, as week 1 of 22/23 has a value.

Now, look what happens when I expand the week 1 of 22/23:

When I filter the data to see only the values for 23/24, I don't see any result for the days:

Moreover, the results are plain wrong.

I see this behavior whenever I collapse the hierarchy level of the previous year.

Is this a bug of the current preview?

The results are correct, but they are only displayed when the days of the previous year are expanded.

This is not intuitive.

Any idea?

Kind regards

Salvatore

[SalvaC]

Thank you again @DataNinja777 

I looked into your proposal, but your calendar isn't entirely correct.

Week 1 of the Fiscal year 23/24 starts on 31. July 2023, but in your Calendar it begins on Tuesday, 01. August 2023.

Here, an extract of the columns set in the custom calendar: https://1drv.ms/x/c/01fa4e143c1d10e8/EXqMV9JIcv1GpHJHRcdFZ6oBplL4tSSRXUCGbdrq4_qGfg?e=de2si8 

Note: I added the Weekday to the calendar as well.

As you can see, the content of the columns in the extract, and therefore in my custom calendar, is the same as in your calendar.

As far as I understand, it shouldn't be necessary to add separate calendar tables to the datamodel for each scenario.

As the filters don't propagate to the columns not added to the calendar, they should be ignored.

I see that your solution works, and I added a calculated table based on data from my Date table:

Fiscal-Week Date =

CALCULATETABLE(

        SUMMARIZECOLUMNS(

                'Date'[FiscalYearForWeek]

                ,'Date'[Fiscal Week/Year]

                ,'Date'[FiscalWeekSort]

                ,'Date'[Day of Week Name]

                ,'Date'[Date]

                ,'Date'[DateKey])

            ,NOT ISBLANK('Date'[FiscalYearForWeek] )  -- For some reason, an empty row was added to the result

            )

I defined a separate calendar and changed the matrix.

Now it works:

Therefore, your proposal works, but this is not the way it should work.

Is anyone here from Microsoft who can say anything about this?

As of today, there is no way to give feedback from Power BI Desktop for this feature.

[v-ssriganesh]

Hi @SalvaC,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @DataNinja777 for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[SalvaC]

Hi

Thank you for your input.

I was off for the weekend.

I will check it out and come back here

[Rufyda]

This is useful, thanks

[DataNinja777 · Accepted solution]

Hi @SalvaC ,

In order to solve your pain points, you can create a calendar table like below:

FiscalWeekCalendar = 
VAR MinDate = MIN( Sales[Date] ) - 370 // Ensure we go back at least one full year
VAR MaxDate = MAX( Sales[Date] )
RETURN
ADDCOLUMNS (
    CALENDAR ( MinDate, MaxDate ),
    
    // 1. Define the Fiscal Year (e.g., "22/23")
    "FiscalYearForWeek",
        VAR y1 = YEAR ( [Date] ) + IF ( MONTH ( [Date] ) >= 8, 0, -1 )
        VAR y2 = y1 + 1
        RETURN FORMAT ( y1, "00" ) & "/" & FORMAT ( y2, "00" ),

    // 2. Define the Fiscal Week Number (e.g., 1, 2, 3... 53)
    // This MUST reset for each new fiscal year
    "FiscalWeekNum", 
        VAR y1 = YEAR ( [Date] ) + IF ( MONTH ( [Date] ) >= 8, 0, -1 )
        VAR FYStartDate = DATE( y1, 8, 1 )
        // Find the start of the first week (e.g., Monday) of that fiscal year
        VAR FirstDayOfFirstWeek = FYStartDate - WEEKDAY( FYStartDate, 2 ) + 1
        // Calculate the week number
        VAR WeekNum = INT( ( [Date] - FirstDayOfFirstWeek ) / 7 ) + 1
        RETURN WeekNum,

    // 3. Define a Sort Column for "Week of Year" (e.g., 202201, 202202...)
    // This gives a unique, sortable value for every week in history
    "FiscalWeekSort", 
        VAR y1 = YEAR ( [Date] ) + IF ( MONTH ( [Date] ) >= 8, 0, -1 )
        VAR FYStartDate = DATE( y1, 8, 1 )
        VAR FirstDayOfFirstWeek = FYStartDate - WEEKDAY( FYStartDate, 2 ) + 1
        VAR WeekNum = INT( ( [Date] - FirstDayOfFirstWeek ) / 7 ) + 1
        // Create a key like YYYYWW
        RETURN (y1 * 100) + WeekNum
)

Then create the matrix as in your example and the resultant output is as shown below:

I have attached an example pbix file for your reference.

Best regards,

[SalvaC]

Hi

After conducting several tests, I concluded that your approach is effective.

Here, a screenshot from my calendar table:

To me, it's still not ideal that I have to generate a separate calendar table when all the necessary columns already exist in the Date table.
However, I hope this issue will be resolved soon.

I'm happy to accept your suggestion as the solution.

Have a nice day, and thank you again.

Salvatore