Forum Discussion
Calculations using XX days / weeks ago
Hi Community,
I know Ibend will know this and I'm sure many of you will as well. I could use some help on calculating a value XX days / weeks ago to finalize a calculation
I have tried:
| 9/3/2023 | 9/10/2023 | 9/17/2023 | 9/24/2023 | 10/1/2023 | 2/25/2024 | 3/3/2024 | 3/10/2024 | 3/17/2024 | 3/24/2024 | ||||||
| Aggregated Non Promotion Thresholds Unit Volume | -1,942 | -2,006 | -2,281 | -2,679 | -3,103 | -18,772 | -19,369 | -19,035 | -18,885 | -20,120 | ||||||
| MULOC + eComm | Fruit | Non Promotion Thresholds Unit Volume | -1,184 | -1,205 | -1,309 | -1,272 | -1,426 | -3,672 | -3,690 | -3,936 | -3,704 | -3,754 | ||||
| MULOC + eComm | Candy | Non Promotion Thresholds Unit Volume | -157 | -177 | -95 | -133 | -114 | -2,976 | -3,060 | -3,067 | -2,950 | -2,938 | ||||
| MULOC + eComm | soda | Non Promotion Thresholds Unit Volume | -368 | -348 | -466 | -398 | -408 | -5,592 | -5,933 | -5,602 | -5,220 | -4,969 | ||||
| MULOC + eComm | chips | Non Promotion Thresholds Unit Volume | 166 | 120 | 113 | 111 | 83 | -1,290 | -1,194 | -1,348 | -1,533 | -2,683 | ||||
| MULOC + eComm | bread | Non Promotion Thresholds Unit Volume | -168 | -230 | -294 | -242 | -231 | -1,983 | -1,937 | -2,104 | -2,286 | -2,128 | ||||
| MULOC + eComm | pretzels | Non Promotion Thresholds Unit Volume | -17 | 23 | 19 | -501 | -757 | -2,880 | -3,115 | -2,526 | -2,765 | -3,269 | ||||
| MULOC + eComm | tacos | Non Promotion Thresholds Unit Volume | -219 | -182 | -237 | -208 | -215 | -377 | -432 | -438 | -423 | -373 | ||||
| MULOC + eComm | ice cream | Non Promotion Thresholds Unit Volume | 5 | -7 | -12 | -36 | -35 | -2 | -8 | -14 | -4 | -6 | ||||
| Dax formula | -7 |
I am trying to calculate the sum for 2/25/24 and 9/3/23 (-7), 3/3/24 and 9/10/24 etc.
Thank you in advance.
Hi Anonymous
Based on your description, you should use the date in calendar table.
You can try the following.
Sum_preperiod = CALCULATE ( SUM ( 'Histo - Reformatted'[Non Promotion Thresholds Unit Volume] ), DATEADD ( 'Calendar'[Date], -180, DAY ) )Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi Anonymous
You can create a calendar table first.
Calendar = CALENDAR(DATE(2023,1,1),DATE(2024,12,31))Then create a 1:N relationship between the tables.
Then create the following measures.
Sum = CALCULATE(SUM('Histo - Reformatted'[Non Promotion Thresholds Unit Volume]))Sum_preperiod = CALCULATE ( SUM ( 'Histo - Reformatted'[Non Promotion Thresholds Unit Volume] ), DATEADD ( 'Calendar'[Date], -5, MONTH ) )Then put the following fields to the matrix visual.
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Yolo,
Thank you for your support.
So, I had a calendar already made as well and the relationship for 1:many.
I had the current Non-promo Threshold calculation in there. But using yours, it did not change the prior calc to 5.
I added another calendar table as you had and added that relationship. I need to see them by weekly increments, not the months in which yours laid out.
I did try the dateadd but with -180 days. I appreciate you help. Any other ideas? Could it be that I have the wrong calendar? It doesn't line up with the heirarchy as your does.
I couldn't imagine it being this difficult. I must have something wrong. I have done it in the past with data sets that don't have last year, and use parallelperiod or sameperiodlastyear. But this case, I need vs. 6 months and I haven't found that options.
Thanks again for your support.
Hi Anonymous
Based on your description, you should use the date in calendar table.
You can try the following.
Sum_preperiod = CALCULATE ( SUM ( 'Histo - Reformatted'[Non Promotion Thresholds Unit Volume] ), DATEADD ( 'Calendar'[Date], -180, DAY ) )Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.