topic Retrieve Quantity for Last Fiscal Week (using Dates Table) in DAX Commands and Tips

Retrieve Quantity for Last Fiscal Week (using Dates Table)

dtg87 — Thu, 26 Mar 2020 16:46:00 GMT

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

DateKey	Quantity	Total Sales
01/01/2020	10	30
02/01/2020	11	33
03/01/2020	12	36
04/01/2020	11	33
05/01/2020	13	39

Date Dim Table (Joined on Date)

DateKey	FiscalYear	FiscalMonth	FiscalDayofYear
01/01/2020	2019	12	361
02/01/2020	2019	12	362
03/01/2020	2019	12	363
04/01/2020	2019	12	364
05/01/2020	2020	1	1

Re: Retrieve Quantity for Last Fiscal Week (using Dates Table)

Greg_Deckler — Thu, 26 Mar 2020 16:31:34 GMT

Would be very helpful if there was sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

Re: Retrieve Quantity for Last Fiscal Week (using Dates Table)

dtg87 — Mon, 30 Mar 2020 11:18:42 GMT

Thanks for the heads up Greg, I have attached the sample data. Your hard way guide is good, however I wish to use selectedvalues and not create rolling totals.

Dave