Rolling Weighted Averag

Hi All,

My data model is consists of many tables the relevant are:

1. Date table- contains a list of dates from 2019 until 2026 (to make sure I will not have to add them manually each time) and information about each date: month, year, etc.)
2. Sales table- list of sales from July- December 2021.

I'm trying to create a rolling weighted average measure. The idea is to sum for each month and X (variable) months before it the revenue, the quantity, and the ratio between them (to get a weighted average).

I created 4 variables: Time Period, Total Revenue, Total Quantity, Ratio. For some reason I got wrong results, I started going through each variable to see what it returns and noticed that the part of the rolling sum gets me to wrong numbers but only in one specific month. It is very strange since this month is in the middle (October)- for this specific month I get a higher number.

This is the formula I wrote for the revenue sum (I that the same solution will solve me the quantity and ratio issue:

* The time period is to -1 because I tried to see if I get the right numbers for each individual month before summing them.

This is what I try to do (in this case for 2 months):

I'm stuck on it for a very long time, your help will be much appreciated.

Thanks,
Eli