Calculations using XX days / weeks ago

[atoice1]

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:

Threshold Difference =

VAR SelectedDate = SELECTEDVALUE('Histo - Reformatted'[Date])

VAR PastDate = SelectedDate - 180

RETURN

CALCULATE(

    SUM('Histo - Reformatted'[Non Promotion Thresholds Unit Volume]),

    'Histo - Reformatted'[Date] = SelectedDate

) - CALCULATE(

    SUM('Histo - Reformatted'[Non Promotion Thresholds Unit Volume]),

    'Histo - Reformatted'[Date] = PastDate

)

I have also tried:

Prior Threshold = calculate(sum('Histo - Reformatted'[Non Promotion Thresholds Unit Volume]),DATEADD('Histo - Reformatted'[Date], -180, day))

The only value returned is the current value as the prior threshold returns 0.

			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.

[atoice1]

Thank you Yolo for your continued help. That was the formula I used originally. I guess it must be something in my calendar that is causing the issue. It may be something I just do in Excel vs. Power BI. I just prefer Power BI. 🙂

[v-xinruzhu-msft]

Hi @atoice1 

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.

[atoice1]

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.

[v-xinruzhu-msft · Accepted solution]

Hi @atoice1 

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.