Rolling Value For Last 3 Months

[ssab_wenx]

Hi All,

I have a question about how to calculate the last 3 months rolling value. 

Let's say I have a below table:

and I want to create a measure to get the rolling 3 before month values(it should calculate by any filter), the result should looks like(if user select the country filter) :

this is a measure I created:

Rolling3monthCostPerTon = 
CALCULATE(
     DIVIDE(SUM([cost]), SUM([ton]), 0),
     DATESBETWEEN(
        'table'[createdate],
        EDATE('table'[createdate], -3),
        'table'[createdate]
    )
)

The result is not what I wanted.

So is there anything wrong about my measure? 

[Ashish_Mathur]

Hi,

for 2024/5/1, why should the rolling cost be 30?  Shouldn't it be 30+80+210 = 320?

[ssab_wenx]

If the user don't filter the record by conuntry, yes it should be 30+80+210 = 320. sorry for the misunderstandings, the screenshot shows the value filter by country.

[Ashish_Mathur · Accepted solution]

Hi,

Try this approach

1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number column
2. Create a relationship (Many to One and Single) from the Date column of the Fact table to the Date column of the Calendar table
3. To your visual, drag Year and Month name from the Calendar table
4. Write these measures

C = sum(Data[Cost])

T = sum(Data[Tonne])

CPT = divide([C],[T])

R3M C = calculate([C],datesbetween(calendar[date],edate(min(calendar[date]),-2),max(calendar[date])))

R3M T = calculate([T],datesbetween(calendar[date],edate(min(calendar[date]),-2),max(calendar[date])))

R3M CPT = divide([R3M C],[R3M T])

Hope this helps.

[ssab_wenx]

Thanks for the reply. It works on local if I don't filter the records. The result becomes wrong if I  set a "Country" slicer. That means it only works on "createdate". Attached the pbix if you are avaliable to take a look. Thanks!

[Ashish_Mathur]

Share the download link of the PBI file.  Show the problem there very clearly.

[ssab_wenx]

Hi Ashish, I cannot upload the PBI file here since I am not a super user. Could you tell me your mail ? Really thanks!

[Ashish_Mathur]

Share the download link of the PBI file.

[ssab_wenx]

never mind, I solved the problem. Thanks!

[bhanu_gautam]

@ssab_wenx Try using

DAX
Rolling3MonthCost =
CALCULATE(
SUM('table'[cost]),
DATESINPERIOD(
'table'[createdate],
MAX('table'[createdate]),
-3,
MONTH
)
)

DAX
Rolling3MonthTon =
CALCULATE(
SUM('table'[ton]),
DATESINPERIOD(
'table'[createdate],
MAX('table'[createdate]),
-3,
MONTH
)
)

DAX
Rolling3MonthCostPerTon =
DIVIDE(
[Rolling3MonthCost],
[Rolling3MonthTon],
0
)

[ssab_wenx]

Hi, thanks for your reply but it doesn't works. As for the cost, it keeps the current row value and not calculate the total cost of last 3 months. For the "MAX('table'[createdate])", what does it mean?  I think it should be the current row data value. Could you help take a look? Thanks!