Rolling 3 months average of a measure value

[Anonymous]

Hi All,

I want to calculate rolling 3 months average of a measure value. 

Provided my dataset below. I am not getting the expected output for "3 months rolling average"

Here "Turnover%" is a measure value not column. 

Provided the expected output below

 

It should sum rolling 3 months and divide by 3 i.e., average for rolling 3 months for a measure value.

Thanks in Advance.

[v-piga-msft · Accepted solution]

Hi @Anonymous,

By my tests, if you have create a calendar table then you could try the formula below.

Moving 3 Months Average =
VAR temp =
    CALCULATE (
        AVERAGEX (
            SUMMARIZE (
                'Data',
                'Calendar table'[Date].[Year],
                'Calendar table'[Date].[Month],
                "sumtotal", [Turnover%]
            ),
            [sumtotal]
        ),
        DATESINPERIOD (
            'Calendar table'[Date],
            LASTDATE ( 'Calendar table'[Date] ),
            -3,
            MONTH
        )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Calendar table'[Date].[MonthNo] ) < 3, BLANK (), temp )

Hope this can help you!

Best  Regards,

Cherry

[Ashish_Mathur · Accepted solution]

Hi,

I believe this is the result you want.  You may download my PBI file from here.  Please note that the active employees as at the end of December 2018 (Total ow) is 1 (not 2 as shown in your screenshot).  Furthermore, the 3 month Moving average for November should be (0+33.33)/2=16.67 and for December it should be (0+33+50)/3=27.78.

Hope this helps.

[ryan_mayu]

@Anonymous

You need to create a datetime table.

Then you use below  coding

rolling 3 mth = 
VAR PM=CALCULATE(SUM('fact'[turnover%]),DATEADD('date'[date],-1,MONTH))
VAR P2M=CALCULATE(SUM('fact'[turnover%]),DATEADD('date'[date],-2,MONTH))
RETURN if(ISBLANK(PM)||ISBLANK(P2M), " ", DIVIDE([turnover]+PM+P2M,3))

Then you will see empty in month, you can unclick empty in the month column in Filters.

Thanks and BR

Ryan

[Anonymous]

Thanks for your reply. Yes, I have a separate date table.

But my turnover% is a measure not column.

[Anonymous]

Provided the updated screenshot

[ryan_mayu]

@Anonymous

Please try to remove SUM,change the coding to 

Calculate(Demographics(Turnover%),DATEADD('Table'[Date],-1,MONTH))

By the way, I think there are two ways of calculating rolling 3 months average.

1. sum of three months' turnover%, then divided by 3.

2. sum of three months' numerators, then divided by sum of three months' denominator.

I think my solution only fits the first scenario. If your scenario is the second one, you need to modify the coding.

Thanks and BR

[Anonymous]

@ryan_mayu

Yes i am trying Firat scenario. I also got the required output but still have 1 problem.

I should not form any association between two (Calendar and main table) tables.

If i delete the association between two tables i am not getting the required output.

I cant create association between these two records because i have hire date and termination date in main table and if i associate with any one of that than other measure which i created earlier are getting impacted. So is there any way to get this output without associating two tables?

[ryan_mayu]

@Anonymous

I only have one table this time. 

rolling 3m = 
VAR maxdate=MAX('Sheet3'[date])
VAR PM=DATE(YEAR(maxdate),MONTH(maxdate)-1,01)
VAR P2M=DATE(YEAR(maxdate),MONTH(maxdate)-2,01)
VAR PMturnover=CALCULATE([turnover%],FILTER(all(Sheet3),'Sheet3'[date]=PM))
VAR P2Mturnover=CALCULATE([turnover%],FILTER(ALL(Sheet3),'Sheet3'[date]=P2M))
RETURN IF(ISBLANK(PMturnover)||ISBLANK(P2Mturnover),BLANK(),DIVIDE([turnover%]+PMturnover+P2Mturnover,3))

Please try the coding above to see if this can meet your requirement.

[Anonymous]

@ryan_mayu

Actually i want 2 different table but dont want to associate them.

Turnover% is present in Sheet1 and Calendar table is different table.

Provided 1 eg. how i calculated terminated employees count without associating two tables.

Terminated_Employee_Counts = 

Calculate(

    COUNT('Main Table'[worthID]),

        FILTER('Main Table',

                COUNTROWS(FILTER(VALUES('Table'[Date]),

                        'Main Table'[LastHireDate] <= 'Table'[Date] &&

                        'Main Table'[TerminationDate] = 'Table'[Date] ))

                > 0)

        )

[ryan_mayu]

@Anonymous

To be honest, I am sure if there is a solution for this.

However, I have a workaround.

1. Creating a new column in Main table

datecheck= if( 'Main Table'[TerminationDate] >='Main Table'[LastHireDate], "Yes","No")

2. Link termination date column to the date table

3. Try the coding below. Maybe you can modify the coding to meet your requirement.

Calculate(

    COUNT('Main Table'[worthID]),

        FILTER('Main Table',

                COUNTROWS(FILTERING('Main Table'[datacheck']="Yes")

                          )

[Anonymous]

@ryan_mayu

Yes i followed the 3 steps which you suggested but as i mentioned earlier i am counting the active employees counts too in the same report.

Active employee counts before assicating the data model

Active Employees = Calculate(

    COUNT('Main Table'[worthID]),

        FILTER('Main Table',

                COUNTROWS(FILTER(VALUES('Table'[Date]),

                        'Main Table'[LastHireDate] <= 'Table'[Date] &&

                        'Main Table'[TDate] >= 'Table'[Date] ))

                > 0 )

        )

But after associating the data model and following the 3 steps which you provided i could see null values in active employees counts.

[Anonymous]

@ryan_mayu

By following your 3 steps and by modifying the terminated employee counts, it is working fine for terminated employee

But getting null values in active employee counts.

Also Provided the explanation for TDate in the below formula for Active employee counts

TDate = If Termination date is null then today’s date else Termination Date

[ryan_mayu]

@Anonymous

What's your active employee's measure?

[Anonymous]

@ryan_mayu

Provided my Active Employee Measure

Active Employees = Calculate(

    COUNT('Main Table'[worthID]),

        FILTER('Main Table',

                COUNTROWS(FILTER(VALUES('Table'[Date]),

                        'Main Table'[LastHireDate] <= 'Table'[Date] &&

                        'Main Table'[TDate] >= 'Table'[Date] ))

                > 0 )

        )

[Vishnu_1980]

Hi

Please try the below DAX query .i have tested with my data.Please have a calendar table and do necessary relatioship

Rolling =
CALCULATE(
AVERAGEX( VALUES( 'Calendar'[Month-Year] ), [Sum of Turnover] ),
DATESINPERIOD( 'Calendar'[Date], MAX( 'Calendar'[Date] ), -3, MONTH ) )

Please refer link :https://community.powerbi.com/t5/Desktop/Rolling-3-Month-Average-of-rows-that-need-to-be-aggregated/...

[v-piga-msft · Accepted solution]

Hi @Anonymous,

By my tests, if you have create a calendar table then you could try the formula below.

Moving 3 Months Average =
VAR temp =
    CALCULATE (
        AVERAGEX (
            SUMMARIZE (
                'Data',
                'Calendar table'[Date].[Year],
                'Calendar table'[Date].[Month],
                "sumtotal", [Turnover%]
            ),
            [sumtotal]
        ),
        DATESINPERIOD (
            'Calendar table'[Date],
            LASTDATE ( 'Calendar table'[Date] ),
            -3,
            MONTH
        )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Calendar table'[Date].[MonthNo] ) < 3, BLANK (), temp )

Hope this can help you!

Best  Regards,

Cherry

[Anonymous]

I was trying your formula but i just want to know what is "Data" in the formuls which you provided.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

Hi @Ashish_Mathur,

provided the Power bi file path

https://avacorp1-my.sharepoint.com/:u:/g/personal/prakash_m_avasoft_com/EbJ3iDZwO1JOjVPvVabZrfkBpSNm...

Here i need to get the rolling 3 months average for december month as 27.6 that is what i am expecting.

I didn't associate the two tables. If i associate then terminated employees and active employees counts are getting changed.

If i follow the sugesstions provided by ryan mayu and v-piga-msft then counts for terminated employees counts are matching but active employee counts are showing null.

[Ashish_Mathur · Accepted solution]

Hi,

I believe this is the result you want.  You may download my PBI file from here.  Please note that the active employees as at the end of December 2018 (Total ow) is 1 (not 2 as shown in your screenshot).  Furthermore, the 3 month Moving average for November should be (0+33.33)/2=16.67 and for December it should be (0+33+50)/3=27.78.

Hope this helps.

[Anonymous]

Hi @Ashish_Mathur,

Thanks for your help.

Yes you are right at the active employees as at the end of December 2018 (Total ow) is 1.

But Turnover% = Terminated/Total Employees

I want another column (Total Employees) where it is almost similar to Active Employees column but in the total i want 3 and the turnover% for Total should be 66.66

Thanks for your help.

[Anonymous]

Hi @Ashish_Mathur,

Also when i select nov and dec month the turnover% is showing infinity and for Active Employees in total it is not showing 1.

I think when we select nov and dec the total employee count will be 3 for those 2 months and turnover% need to be (2/3)*100 i.e., (terminated employee) / Total employees * 100

So still there is some issue in active employee counts and i require Total Employee counts to calculate Turnover%.

Thanks in advance.