Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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: Th...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.