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.

For 4-4-5 accounting calendar implementation in Java, see `AccountingChronology` and `AccountingChronologyBuilder` ([Javadoc](https://www.threeten.org/threeten-extra/apidocs/org.threeten.extra/org/threeten/extra/chrono/package-summary.html)) in the [*ThreeTen-Extra*](https://www.threeten.org/threeten-extra/) project. — Basil Bourque, Apr 23 '20 at 21:52

Axel Richter · Accepted Answer · 2020-04-16T14:51:15.203

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.

edited Apr 16 '20 at 14:51

answered Apr 16 '20 at 13:32

Axel Richter

56,077
6
60
87

Thanks for that awesome approach! It works perfectly. How would you calculate the fiscal week for each date in a calendar table based on these information? – Aaron Apr 20 '20 at 14:03
2

@joshua: This is not how Stackoverflow works. If you have a new question then ask a new question. If this is a follow up question to another question the do referencing that other question in your new question. And please be more verbose in your question. What is a fiscal week for you compared to a calendar week? What calendar table are you talking about? And what means "based on these information" in that context? Best way is providing what you have, what you have tried, what was the result of that and what are you expecting to be the result. – Axel Richter Apr 20 '20 at 14:14

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

1 Answers1