Prior Week Of Sales - easy way

[ebrownretail]

Hi!

I have been working on trying to get Prior Week of Sales to come through as a DAX Measure, and noticed many of them are very complicated. Below is the formula i have used for a couple weeks now and it moves with me as current dates change. i work with current information for product inventory replenishment.

To do this, you will need a Dates table that has week numbers listed and Table with your sales history. Replace the Colored with your tables/column names as needed - same color mean same report/column.

Prior Week Sales:

CALCULATE(SUMX('All Sales History','All Sales History'[POS Sales]), FILTER('All Sales History',CALCULATE(MAX(Dates[Fiscal Week]),FILTER(Dates,Dates[Date] = TODAY()-1))))

[v-linhuizh-msft · Accepted solution]

Your solution is great @Shravan133 . 

Hi @ebrownretail  ,

If you are trying to share what you are doing, you can reply to yourself and accept it as a solution to help other members find it faster. Here I have another idea in mind, and I would like to share it for reference.

1.Create simple data:

The relationship between them is:

2.Create a measure. Checks if the selected value of POS Sales in the All Sales History table is not blank. Applies a filter to select records where the Fiscal Week in the Dates table is equal to ‘_current_week – 1’, effectively getting the sales data for the prior week.

Prior Week Sales =
VAR _current_week = SELECTEDVALUE(Dates[Fiscal Week])
RETURN IF(SELECTEDVALUE('All Sales History'[POS Sales])<>BLANK(),CALCULATE(SUM('All Sales History'[POS Sales]),ALL('All Sales History'),'Dates'[Fiscal Week]=_current_week-1))

3.The final result is as follows:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Shravan133]

Hi, 

Try this: 

• Get the current fiscal week and year.
• Determine the prior week and adjust for year boundaries.
• Sum the sales for the prior week.

Prior Week Sales =
VAR CurrentDate = TODAY()
VAR CurrentWeek = MAXX(FILTER(Dates, Dates[Date] = CurrentDate), Dates[Fiscal Week])
VAR CurrentYear = MAXX(FILTER(Dates, Dates[Date] = CurrentDate), Dates[Year])
VAR PriorWeek = IF(CurrentWeek = 1, 52, CurrentWeek - 1) -- Adjust for year boundaries
VAR PriorYear =IF(CurrentWeek = 1, CurrentYear - 1, CurrentYear)

RETURN

CALCULATE(SUM('All Sales History'[POS Sales]),

FILTER(ALL(Dates),Dates[Fiscal Week] = PriorWeek && Dates[Year] = PriorYear))