Forum Discussion

powerbiexpert22's avatar
powerbiexpert22
Icon for Impactful Individual rankImpactful Individual
2 years ago
Solved

rolling 3 months sum

in case of rolling 3 months amount (not rolling day) calculation, should i consider time period between current date-3 months and current date  or it should be between the last date of current month - 3 months and last date of current month?

 

example let say if today is 20th July 2024 ,  if i need to calculate 3 months rolling sum of sales amount then which one of the below mentioned criterias i should consider for calculating the rolling sum 

 

criteria 1:  calculate sum of the amount between 20th April 2024 ( 20th July 2024 - 3 months) and 20th July 2024 

criteria 2:  calculate sum of the amount between 30th April 2024 ( 31st July 2024 - 3 months) and 31th July 2024  

 

Need Help
  • shafiz_p's avatar
    shafiz_p
    2 years ago

    Hi, powerbiexpert22  The rolling sum calculation is indeed related to the maximum date in your date table. In case of July, and the maximum date in your table is the 20th. In that case, this date will be used to evaluate the rolling sum. However, if the maximum date is the 31st, then that date will be used instead. 


    I hope this clarifies your question, if, please mark it as a solution!!

3 Replies

  • shafiz_p's avatar
    shafiz_p
    Icon for Super User rankSuper User
    2 years ago

    Hi, powerbiexpert22  The rolling sum calculation is indeed related to the maximum date in your date table. In case of July, and the maximum date in your table is the 20th. In that case, this date will be used to evaluate the rolling sum. However, if the maximum date is the 31st, then that date will be used instead. 


    I hope this clarifies your question, if, please mark it as a solution!!

  • Joe_Barry's avatar
    Joe_Barry
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Hi powerbiexpert22 

    The below measure will show the average at a day level for the last 3 months. There is more information on this here https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

    Sales R3M =
VAR NumOfMonths = 3
VAR LastCurrentDate =
    MAX ( 'Calendar'[Date] )
VAR Period =
    DATESINPERIOD ( 'Calendar'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
    CALCULATE (
        AVERAGEX (
            VALUES ( 'Calendar'[Date] ),
            [Total Sales]
        ),
        Period
    )
VAR FirstDateInPeriod = MINX ( Period, 'Calendar'[Date] )
VAR LastDateWithSales = MAX ( Sales[Order Date] ) ///Enter the date that you want to calculate on
RETURN
    IF ( FirstDateInPeriod <= LastDateWithSales, Result )

     Hope this helps

    Joe

  • Ray_Minds's avatar
    Ray_Minds
    Icon for Solution Supplier rankSolution Supplier
    2 years ago

    Hi powerbiexpert22 

    Step1: Ensure you have a Date table in your Power BI model. If you don't have one, create it using the following DAX

    DateTable =
    ADDCOLUMNS (
        CALENDAR (MIN('YourTable'[DateColumn]), MAX('YourTable'[DateColumn])),
        "Year", YEAR([Date]),
        "Month", MONTH([Date]),
        "MonthEnd", EOMONTH([Date], 0)
    )

    Note- Replace 'YourTable'[DateColumn] with your actual date column.

    Step2) Create the Rolling 3-Month Measure

    Rolling 3-Month Sales =
    CALCULATE (
        SUM('YourTable'[SalesAmount]),
        DATESBETWEEN(
            'DateTable'[Date],
            EOMONTH(MAX('DateTable'[Date]), -3) + 1,
            EOMONTH(MAX('DateTable'[Date]), 0)
        )
    )

    Also you can watch this video
    3 Months Rolling Total in Power BI | How to calculate 3 months rolling total in Power BI | - YouTube


    If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.