Forum Discussion

SalvaC's avatar
SalvaC
Icon for Advocate III rankAdvocate III
11 months ago
Solved

Strange behavior with custom weekly calendar

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 definiti...
Need Help
  • DataNinja777's avatar
    DataNinja777
    11 months ago

    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's avatar
SalvaC
Icon for Advocate III rankAdvocate III
11 months ago

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.