Forum Discussion

ebrownretail's avatar
ebrownretail
Icon for Resolver I rankResolver I
2 years ago
Solved

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 ...
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Shravan133
Icon for Super User rankSuper User
2 years ago

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