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

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!

1 Like

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

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

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.

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

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.

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

This is what I get using these

1 Like

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.

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

A post was split to a new topic: Week Level Data

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

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

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?

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

1 Like

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?