4-4-5 Calendar approach based on Java 8 Code

Question

For my Fiscal Calendar in Power BI I am currently trying to implement the 4-4-5 approach.

Our calendar works with 4-4-5 week quarters. Since this only has 364 days each year, there must be a 53 week year after a few years. As a result, December has 6 instead of 5 weeks. Unfortunately, there is still no approach based on DAX. In another post here, however, I found a JAVA code, which probably determines whether the year has 53 weeks or not: calculate number of weeks in a given year

    private static long getNumberOfWeeksInYear(LocalDate date) {
    LocalDate middleOfYear = date.withDayOfMonth(1).withMonth(6);
    return middleOfYear.range(WeekFields.ISO.weekOfWeekBasedYear()).getMaximum();
}

public static void main(String[] args) {
    for (int year = 2000; year < 2400; year++) {
        long numberOfWeeks = getNumberOfWeeksInYear(LocalDate.of(year, 1, 1));
        if (numberOfWeeks != 52) {
            System.out.println(year + " has " + numberOfWeeks + " weeks");
        }
    }
}

Do any of you know how to translate the code into Dax?

Our Fiscal Calendar starts not based on the gregorian calendar. This year starts at 30.12.19 and ends 03.01.21. This year has 53 weeks.

Answer 1

I cannot help with PowerPivot and DAX. But I can tell you how to get those information usingExcel formulas.

Given the year in A2 you can calculate the Monday of the first ISO calendar week in that year using following formula:

=DATE($A2,1,1)-WEEKDAY(DATE($A2,1,1),3)+(ISOWEEKNUM(DATE($A2,1,1)-WEEKDAY(DATE($A2,1,1),3))<>1)*7

You can calculate the Sunday of the last ISO calendar week in that year using following formula:

=DATE($A2+1,1,1)-WEEKDAY(DATE($A2+1,1,1),3)+(ISOWEEKNUM(DATE($A2+1,1,1)-WEEKDAY(DATE($A2+1,1,1),3))<>1)*7-1

Given the Sunday of the last ISO calendar week placed in C2, following formula calculates the number of ISO weeks of that year:

=ISOWEEKNUM($C2)

Example:

As you see, the years are from A2 downwards. Formula to calculate the Monday of the first ISO calendar week is placed in B2 downwards. Formula to calculate the Sunday of the last ISO calendar week is in C2 downwards. And the formula to calculate the number of ISO weeks of that year is in D2 downwards.