Cumulative sales through same week last year

DAX DAX Calculations
12

Hi @kjssdca,

Let’s go over what changed to compare Weeknumber CY with the same Weekno in the LY.

  1. Updated the Date table M function to include ISO Year, ISO Week and ISO Day of Year, you can find that here: Extended Date Table (Power Query M function)
    image
    image1311×345 10.3 KB

    NOTE. If your business logic for Weeknumbers is different, you’ll have to update the logic for columns that contain ISO* in the name. Doing that will affect other columns in the Date table.
  2. The most important addition to the Date table for your requirement is ISO Day of Year. This counts the days in the ISO Year and is therefore at the same granularity as the Date column but at an offset to it, because it always starts on the first Monday in ISO week no 1 (full calendar years). This alignment is what makes the same week comparison over years possible.
  3. The basic cumulative pattern: FILTER( ALL( Dates[Date] ), Dates[Date] <= MAX( Dates[Date] )) could be replaced by: FILTER ( ALL( Dates[ISO Day of Year] ), Dates[ISO Day of Year] <= MAX( Dates[ISO Day of Year] )) although we’ll also incorporate the ISO Year in the full pattern.
  4. please review this article (also referenced above): Week based time intelligence

Here are the measures created:

Sales Current Week = 
CALCULATE( [Total Sales],
    FILTER( ALL( Dates ),
      Dates[ISO Weeknumber] = SELECTEDVALUE( Dates[ISO Weeknumber] ) && 
      Dates[ISO Year] = SELECTEDVALUE( Dates[ISO Year] )
    )
)

.

Same Week Last Year = 
IF( HASONEVALUE( Dates[ISO Year] ) && HASONEVALUE( Dates[ISO Weeknumber] ), 
    CALCULATE( [Total Sales],
        FILTER( ALL( Dates ),
            Dates[ISO Year] = VALUES( Dates[ISO Year] )-1
                && Dates[ISO Weeknumber] = VALUES( Dates[ISO Weeknumber] )
                && Dates[Date] <= MAX( Dates[Date] )
        )
    ))

.

Cum Sales CY = 
    CALCULATE( [Total Sales], 
        FILTER( ALL(Dates[Date]), 
            Dates[Date] <= MAX(Dates[Date])), 
        VALUES(Dates[ISO Year])
    )

.

Cum Sales LY = 
IF( HASONEVALUE( Dates[ISO Year] ),
    CALCULATE( [Total Sales],
        FILTER( ALL( Dates ),
            Dates[ISO Year] = VALUES ( Dates[ISO Year] ) -1 &&
            Dates[ISO Day of Year] <= MAX( Dates[ISO Day of Year] )
        )
    ))

.
I hope this is helpful. File with updated Date table below.
Cumulative ISO Week vs ISO Week LY.pbix (440.2 KB)

2 Likes