Forum Discussion

jimpatel's avatar
jimpatel
Post Patron
2 years ago
Solved

3 months rolling sum

Hi,

 

Thanks for looking at my post. I am looking for some DAX formula for 3 months rolling sum of calculated column please? Any idea please?

Thanks a lot

 

rolling sum
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi lbendlin ,Thanks for your quick reply, I will add more.

    Hi jimpatel ,

    The Table data is shown below:

    Use the following DAX expression to create columns.

    Column = 
YEAR('Table'[Date]) * 100 + MONTH('Table'[Date])
    Rank = 
RANKX('Table',[Column],,ASC,Dense)
    3 months rolling sum = 
VAR _rank = [Rank]
VAR _table = SUMMARIZE('Table',[Rank],[Calculated Column])
RETURN
SUMX(FILTER(_table,[Rank] <= _rank && [Rank] >= _rank - 2),[Calculated Column])

    Final output

     

    Best Regards,
    Wenbin Zhou
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi lbendlin ,Thanks for your quick reply, I will add more.

    Hi jimpatel ,

    The Table data is shown below:

    Use the following DAX expression to create columns.

    Column = 
YEAR('Table'[Date]) * 100 + MONTH('Table'[Date])
    Rank = 
RANKX('Table',[Column],,ASC,Dense)
    3 months rolling sum = 
VAR _rank = [Rank]
VAR _table = SUMMARIZE('Table',[Rank],[Calculated Column])
RETURN
SUMX(FILTER(_table,[Rank] <= _rank && [Rank] >= _rank - 2),[Calculated Column])

    Final output

     

    Best Regards,
    Wenbin Zhou
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Sounds straightforward (apart from the data duplication - why a calculated column?). What have you tried and where are you stuck?

  • jimpatel's avatar
    jimpatel
    Post Patron
    2 years ago

    Thanks for your reply. I got stuck with "3 months rolling Sum" section. The answer shown is expected.

     

    Thanks a lot

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      Please provide sample data that fully covers your issue. Show more than three months of data.  Ideally in usable format, not as a screenshot.
      Please show the expected outcome based on the sample data you provided.