Non-Standard Time Intelligence

The available DAX date intelligence functions in Power BI handle the comparison with previous periods easily for the standard periods of year, quarter, month, and day when using a standard calendar, or a standard fiscal calendar (where quarters start on a 3-month cadence, e.g., April 1, July 1, etc., and aligned to [but offset from] calendar quarters). These functions do not, however, handle weekly comparisons or custom fiscal calendars. Calculation techniques to handle week comparisons and non-standard fiscal calendars are presented below.

NOTE: The weekly examples presented below depend on the presence of a continuous Date table, with one row per day covering at least the full analysis interval.

Also, this Date table must be marked as such in Power BI.

A general pattern can be applied to achieve many weekly calculations. The main sections in this general pattern are:

  • Get the current period
  • Get the current year
  • Get the maximum periods in the selected date range
  • Use the SUMX iterator to sum your base measures as follows:
    • If the current period is “1”, get the last period of the previous year
    • If the current period is not “1”, get the previous period of the current year

Weekly Comparisons:

As with everything in Power BI, context must be taken into account, and note the following 2 examples use the year and week numbers from their respective contexts. Using the standard year and week:

Sales LW = 
// DAX PATTERN NAME: Non-standard Time Intelligence
// PATTERN VERSION: Weekly Total Last Week
// NOTES: Use the SUMX iterator function to calculate the total value for the previous week
// - includes logic to correctly handle the first week of the year, where the total calculated is the value from the last week of the last year
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
VAR _CurrentPeriod = SELECTEDVALUE( Dates[WeekOfYear] ) 
VAR _CurrentYear = SELECTEDVALUE( Dates[Year] ) 
VAR _MaxPeriods = CALCULATE( MAX( Dates[WeekOfYear] ), ALLSELECTED( Dates ) ) 
VAR _Result = SUMX(
    FILTER(
        ALL( Dates ),
        IF(
            _CurrentPeriod = 1,
            Dates[WeekOfYear] = _MaxPeriods && Dates[Year] = _CurrentYear - 1,
            Dates[WeekOfYear] = _CurrentPeriod - 1 && Dates[Year] = _CurrentYear
        )
    ),
    [Total Sales]
) 

RETURN
_Result

To get the sales for the same week last year, the pattern is even easier as:

Sales SWLY = 
// DAX PATTERN NAME: Non-standard Time Intelligence
// PATTERN VERSION: Total Same Week Last Year
// NOTES: Use the SUMX iterator function to CALCULATE the total VALUE for the same fiscal period last year
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
VAR _CurrentPeriod = SELECTEDVALUE( Dates[WeekOfYear] ) 
VAR _CurrentYear = SELECTEDVALUE( Dates[Year] ) 
VAR _Result = SUMX(
    FILTER(
        ALL( Dates ),
        Dates[WeekOfYear] = _CurrentPeriod && Dates[Year] = _CurrentYear - 1
    ),
    [Total Sales 445]
) 

RETURN
_Result

As with everything in Power BI, context must always be taken into account, and the same two calculations may be applied to weeks using the ISO calendar (see the [Sales LW ISO] and [Sales SWLY ISO] measures in the attached example).

If grand totals are desired as well, these formulae may be re-written using SELECTCOLUMNS, ADDCOLUMNS, and EARLIER to iterate over all selected periods, not just the current period:

Sales LW ISO (with Grand Total) = 
// DAX PATTERN NAME: Non-standard Time Intelligence
// PATTERN VERSION: Weekly Total Last Week with Grand Total (using ISO Calendar)
// NOTES: Use the SUMX iterator function to calculate the total value for the previous week
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
VAR _ListOfWeeks = SELECTCOLUMNS(
    ADDCOLUMNS(
        VALUES( Dates[WeeknYear] ),
        "@LW", CALCULATE( 
            MAX( Dates[WeeknYear] ),
            FILTER( 
                ALL( Dates ), 
                Dates[WeeknYear] < EARLIER( Dates[WeeknYear] ) )
        )
    ),
    "Weeks", [@LW] 
) 
VAR _Result = SUMX( 
    FILTER( 
        ALL( Dates ), 
        Dates[WeeknYear] IN _ListOfWeeks ), 
    [Total Sales] 
) 

RETURN
_Result

Custom Fiscal Period Calendars

In order to apply DAX formulae when using custom fiscal calendars, one needs to first setup the custom fiscal calendars. Shout out to @Melissa who created the helper table (used in the example PBIX below) containing functions that implemented 4-4-5, 4-5-4, 5-4-4, and 13-period custom calendars based on an article by Ken Puls.
https://www.excelguru.ca/blog/2016/01/06/creating-a-custom-calendar-in-power-query/

Custom Fiscal Period Calendars – 4-4-5

445 fiscal calendars differ from standard calendars, and one must always ensure that the calendar used in your Power BI report is aligned with the organization. In this example, the 445 calendar has the following notable characteristics:

  • 1 year consisting of 52 weeks
  • 4 quarters, each consisting of 13 weeks
  • 12 months, grouped into quarters, with each quarter consisting of 3 “months”; the first 2 months having 4 weeks and the third month having 5 weeks (hence 445)

NOTE: The weekly examples presented below depend on the presence of a continuous Date445 table, with one row per day covering at least the full analysis interval.

As the attached PBIX file (below) uses multiple calendars (necessary for this example but which is very unlikely in real life), a modified base measure is also needed to use the inactive relationship in this example data model:

Total Sales 445 = 
CALCULATE( [Total Sales],
    USERELATIONSHIP( Dates445[Date445], Sales[Order Date] )
    )

Again, after taking context into account, the total sales for the last week using the 445 calendar can be written as:

Sales LW 445 = 
// DAX PATTERN NAME: Non-standard Time Intelligence
// PATTERN VERSION: Total Last Week (using 4-4-5 Fiscal Calendar)
// NOTES: Use the SUMX iterator function to calculate the total value for the previous fiscal week
// - includes logic to correctly handle the first week of the fiscal year, where the total calculated is the value from the last week of the previous fiscal year
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
VAR _CurrentPeriod = SELECTEDVALUE( Dates445[FY Week] )
VAR _CurrentYear = SELECTEDVALUE( Dates445[FY Year] )
VAR _MaxPeriods = 52 -- always 52 weeks in a 445 calendar
VAR _Result = SUMX(
    FILTER(
        ALL( Dates445 ),
        IF(
            _CurrentPeriod = 1,
            Dates445[FY Week] = _MaxPeriods && Dates445[FY Year] = _CurrentYear - 1,
            Dates445[FY Week] = _CurrentPeriod - 1 && Dates445[FY Year] = _CurrentYear
        )
    ),
    [Total Sales 445]
) 

RETURN
_Result

Similarly, measures using the same pattern can be developed for:

  • [Sales LP 445] (last month/period) (may perhaps be useful, but care should be taken when making monthly/period comparisons using the 445 calendar, as the previous month/period may be a different duration (number of weeks) than the current month; regardless, but a code sample is included in the example to illustrate the use of the pattern)
  • [Sales LQ 445]
  • [Sales LY 445]
  • [Sales SPLY 445] (same period last year)
  • [Sales SQLY 445] (same quarter last year)

NOTE: This same technique can be applied also to 4-5-4 and 5-4-4 calendars as well, but examples are not included in the attached example PBIX file.

Custom Fiscal Period Calendars – 13-Period

13-period fiscal calendars differ from standard calendars, and one must always ensure that the calendar used in your Power BI report is aligned with the organization. In this example, the 13-period calendar has the following notable characteristics:

  • 1 year consisting of 13 periods (hence 13 period)
  • 4 quarters, with the first 3 quarters consisting of 3 periods, and the 4th quarter consisting of 4 periods

NOTE: The 13-period custom fiscal calendar examples presented below depend on the presence of a continuous Date13 table, with one row per day covering at least the full analysis interval.

As the attached PBIX file (below) uses multiple calendars (necessary for this example but which is very unlikely in real life), a modified base measure is also needed to use the inactive relationship in this example data model:

Total Sales 13 Period = 
CALCULATE( [Total Sales],
    USERELATIONSHIP( Dates13Period[Date13P], Sales[Order Date] )
    )

Again, after taking context into account, the total sales for the last week using the 13 period calendar can be written as:

Sales LW 13P = 
// DAX PATTERN NAME: Non-standard Time Intelligence
// PATTERN VERSION: Weekly Total Last Week (using 13 Period Calendar)
// NOTES: Use the SUMX iterator function to calculate the total value for the previous week
// - includes logic to correctly handle the first week of the year, where the total calculated is the value from the last week of the last year
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
VAR _CurrentPeriod = SELECTEDVALUE( Dates13Period[FY Week] ) 
VAR _CurrentYear = SELECTEDVALUE( Dates13Period[FY Year] ) 
VAR _MaxPeriods = CALCULATE( MAX( Dates13Period[FY Week] ), ALLSELECTED( Dates13Period ) ) 
VAR _Result = SUMX(
    FILTER(
        ALL( Dates13Period ),
        IF(
            _CurrentPeriod = 1,
            Dates13Period[FY Week] = _MaxPeriods && Dates13Period[FY Year] = _CurrentYear - 1,
            Dates13Period[FY Week] = _CurrentPeriod - 1 && Dates13Period[FY Year] = _CurrentYear
        )
    ),
    [Total Sales 13 Period]
) 

RETURN
_Result

Similarly, measures using the same pattern can be developed for:

  • [Sales LP 13P] (last period)
  • [Sales LQ 13P]
  • [Sales LY 13P]
  • [Sales SPLY 13P] (same period last year)
  • [Sales SQLY 13P] (same quarter last year)

DAX Patterns - Non-Standard Time Intelligence.pbix (463.0 KB)

2 Likes

Just adding some keywords to make this pattern easier to locate via forum search:

DAX pattern, time intelligence, week, fiscal period, 13-period, 4-4-5, 4-5-4, 5-4-4, same period last year, same week last year.

1 Like

Related Content:

There are myriad examples available dealing with non-standard time period analyses. Here are just are a few examples of issues related to the Non-Standard Time Intelligence DAX Pattern from the eDNA resources.

Enterprise DNA Courses:

Enterprise DNA Forum (Search):

https://forum.enterprisedna.co/t/data-modelling-base-on-custom-time-intelligence-format/1001/2

Enterprise DNA TV (YouTube):

Hi @Greg,

I have used the your formula " Sales LW ISO (with Grand Total)" to create my sales last week measure. It works fine in a table with FY & WK, but I need to do this by product and it doesn’t seem to be calculating correctly. The “Total Last Week” measure seems to be adding total this week to total last week.

Would you know where I am going wrong?

image
image

HI @Nurry90. Please attach your work-in-progress PBIX file (with sanitized data, if necessary) for the forum members to pursue. Also, to increase the visibility of your issue, please create a new topic instead of replying to a previously-solved thread.
Greg