Forum Discussion

ssab_wenx's avatar
ssab_wenx
Frequent Visitor
1 year ago
Solved

Rolling Value For Last 3 Months

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 valu...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    1 year ago

    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.

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

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's avatar
    ssab_wenx
    Frequent Visitor
    1 year ago

    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!