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

[AmberM]

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!

[sam.mckay]

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))

[AmberM]

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

[sam.mckay]

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.

[AmberM]

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

[sam.mckay]

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.

[sam.mckay]

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] )

[sam.mckay]

This is what I get using these

[AmberM]

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.

[sam.mckay]

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

Data Transformations & Modeling

[Nurry90]

Hi All,

Silly question, but how did you manage to get the “Total” of total sales. I want to create a table over 2-3 years and need totals to calculate. My “Total” for my sales at the minute is a total of ALL years’ sales. How can I get the total of below?

CurrentYearSales =
VAR CurrentFinWeek = MAX(‘Date Table’[FY Week number])
VAR CurrentFinYear = MAX(‘Date Table’[Fin Yr])
RETURN
CALCULATE ([Total Sales],
FILTER(ALL(‘Date Table’),
‘Date Table’[Fin Yr]=CurrentFinYear &&
‘Date Table’[FY Week number] = CurrentFinWeek))

[AmberM]

I had to create a separate table that grouped the weeks by the timeframes I wanted to define.

[Nurry90]

Hi Amber,

Thanks for getting back to me.

Just wondering how you did this? Do you mean a completely separate table with just FY & FY week?

[AmberM]

A separate table that groups the week names, for example - TY L4 = 202001,202002,202003,202004
You would have a separate static date table that supplies the same week names this year vs last year so that you can use sameperiodlastyear for your calcs

[Nurry90]

Hi Amber,

Do you have a screenshot example of your static table so I can compare? I didn’t think SAMEPERIODLASTYEAR worked for custom date table?