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 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? 

 

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.

10 Replies

  • 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!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    Hi,

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

    • ssab_wenx's avatar
      ssab_wenx
      Frequent Visitor
      1 year ago

      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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        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.