Forum Discussion
Prior Week Of Sales - easy way
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:
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 TeamIf 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!
2 Replies
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))
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 TeamIf 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!
