Same Period Last Year for Fiscal Week Nbr (weeks in sales table are non standard)


#1

I am trying to create a expression to compare sameperiodlastyear at a fiscal week level.

The calculation I tried (below) does not return the correct value.
Total Sales LY = CALCULATE( [Total Sales], SAMEPERIODLASTYEAR( Dates[Date] ) )

My data is at a fiscal week level, and does not have standard calendar dates
For example, I need to compare Fiscal Week 201701 to Fiscal Week 201601, but the fiscal week begins in February. I would like the calculation to be dynamic so that I can look at last 2 weeks, last 4 weeks, etc vs the same period last year?

I haven’t been able to find a solution that seems to work in this situation? Any help is appreciated!


#2

Hi there,

Yes as you’ve seen unfortunately time intelligence calculations don’t work for fiscal weeks.

Here’s are some videos that talk through how to solve this.

This is the ultimate formula you would need

Previous Year Sales = 
VAR CurrentMonth = SELECTEDVALUE( Dates[Week Number] )
VAR CurrentYear = SELECTEDVALUE( Dates[Year] )

RETURN
CALCULATE( [Total Sales],
    FILTER( ALL( Dates ),
		Dates[Week Number] = CurrentMonth && Dates[Year] = CurrentYear - 1))

Current Week Calculation
#3

Thanks Sam! I will try this out and see if it works!!!


#4

I just realised that the variable should actually be named CurrentWeek instead of CurrentMonth, just to be more correct.

Replace it in the actual measure as well once that’s done…simple fix.


#5

Worked like a charm! Thanks so much! Is there a way to get the totals to show at the bottom?


#6

This is a tough one actually, spent a bit of time on it.

There may be a better way and I will try to find it.

Create another measure with this formula and it should work for you.

Previous Year Sales 2 = 
SUMX( 
    SUMMARIZE( Dates, Dates[Year & Week], Dates[Week Number],
    "LY Sales", [Previous Year Sales] ),
        [LY Sales] )

Preferably this should be in the same formula so I will keep looking for a better solution, but this should work for now.


#7

Here’s a better way to solve it. Follow these three steps.

First create this measure

PY Sales Setup = 
VAR CurrentWeek = SELECTEDVALUE( Dates[Week Number] )
VAR CurrentYear = SELECTEDVALUE( Dates[Year] )

RETURN
CALCULATE( [Total Sales], FILTER( ALL( Dates ), Dates[Week Number] = CurrentWeek && Dates[Year] = CurrentYear - 1 ) )

Then this one (use this in the table)

PY Sales = 
SUMX( 
    SUMMARIZE( Dates, Dates[Year & Week], Dates[Week Number],
    "LY Sales", [PY Sales Setup] ),
        [LY Sales] )

Then to work out the change use this

Sales Chg YoY = IF( ISBLANK( [PY Sales] ), BLANK(), [Total Sales] - [PY Sales] )

#8

This is what I get using these

image


#9

That worked! Thanks so much! I have worked with other BI platforms in the past and trying to figure out how to recreate the dataloads/warehouse in the backend. I can send you the ideas I have to make the calcs dynamic but I am still trying to understand the best way to roll up flat files.


#10

Certainly check out the Advanced Modeling Course as this is where I place all my best practices around modeling and data transformations