Forum Discussion

dtg87's avatar
dtg87
Frequent Visitor
6 years ago

Retrieve Quantity for Last Fiscal Week (using Dates Table)

Hi,

 

I am attempting to create a measure that will show the quantity for the last fiscal week to then us to create % var. I have working statment but it isn't taken into account where the fiscalweek of the year = 1 and then needs to refer to the max fiscal week of the previous year (52/53). Can anyone see where i am going wrong on the below. The issue is formula for retrieving VAR MXWeekLY. Thanks!

 

Week LW Quantity:=
VAR CurrentYear = SELECTEDVALUE(Dates[FiscalYear])
VAR CurrentWeek = SELECTEDVALUE(Dates[FiscalWeekOfYear])
VAR MXWeekLY =MAX(Dates[FiscalWeekOfYear],FILTER(ALL(Dates),Dates[FiscalYear]=CurrentYear-1))
RETURN CALCULATE([Quantity],FILTER(ALL(Dates),Dates[FiscalYear]=IF(CurrentWeek=1, CurrentYear-1,CurrentYear) && Dates[FiscalWeekOfYear]=IF(CurrentWeek=1,MXWeekLY,CurrentWeek-1)
))

 

Table containing Quantity

DateKeyQuantityTotal Sales
01/01/20201030
02/01/20201133
03/01/20201236
04/01/20201133
05/01/20201339

 

 

Date Dim Table (Joined on Date)

DateKeyFiscalYearFiscalMonthFiscalDayofYear
01/01/2020201912361
02/01/2020201912362
03/01/2020201912363
04/01/2020201912364
05/01/2020202011

2 Replies