Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculate 3 months rolling

 

Looking for advice on how to calculate 3 months rolling average when data are 0 for some months.

I would like to calculate for Full Year but actual data should be used when available.

The first calculation should come in April and use Data for AVERAGE JAN, FEB, MAR

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    I have created a simple sample, please refer to my pbix file to see if it helps you.

    result =
VAR _1 =
    EDATE ( MAX ( 'Table'[date] ), -3 )
VAR _year =
    YEAR ( _1 )
VAR _month =
    MONTH ( _1 )
VAR _date =
    DATE ( _year, _month, 1 )
VAR _nowdate =
    MAX ( 'Table'[date] )
RETURN
    CALCULATE (
        AVERAGE ( 'Table'[value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[date] >= _date && 'Table'[date] < _nowdate )
    )

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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

     

     

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years 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 column by the Month number
    2. Create a relationship (Many to One and Single) from the Date column of your Data 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

    Measure = sum(Data[Revenue])

    Rolling average = averagex(SUMMARIZE(calculatetable(Calendar,datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date]))),calendar[year],calendar[month name],"ABCD",[Measure]),[ABCD])

    Hope this helps.

3 Replies

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

    Anonymous 


    Can you share some sample data with the desired output to have a clear understanding of your question?
    Mention whether you want a calculated column or measure.
    You can either paste your data in the reply box or save it in OneDrive, Google Drive, or any other cloud-sharing platform and share the link here.


     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    I have created a simple sample, please refer to my pbix file to see if it helps you.

    result =
VAR _1 =
    EDATE ( MAX ( 'Table'[date] ), -3 )
VAR _year =
    YEAR ( _1 )
VAR _month =
    MONTH ( _1 )
VAR _date =
    DATE ( _year, _month, 1 )
VAR _nowdate =
    MAX ( 'Table'[date] )
RETURN
    CALCULATE (
        AVERAGE ( 'Table'[value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[date] >= _date && 'Table'[date] < _nowdate )
    )

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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

     

     

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years 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 column by the Month number
    2. Create a relationship (Many to One and Single) from the Date column of your Data 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

    Measure = sum(Data[Revenue])

    Rolling average = averagex(SUMMARIZE(calculatetable(Calendar,datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date]))),calendar[year],calendar[month name],"ABCD",[Measure]),[ABCD])

    Hope this helps.