cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AndreBV
Frequent Visitor

Not able to create a monthly moving average based on a calculated turnover percentage

Hi everyone, I hope this community can help me with a solution to my problem. My googling have not yet given any results unfortunately.

 

My problem is:

I have a report named 'HR Trended Turnover' which have one row per supervisory organization.

My dashboard have slicers where organizations can be seleced. 

HR Trended Turnover.jpg

 

 

In order to calculate the turnover percentage, I have created a quick measure called 'Frivillig turnover i prosent':

 

Frivillig turnover i prosent =
VAR __BASELINE_VALUE = SUM('NO-SSC HR Trended Turnover'[Average Headcount])
VAR __VALUE_TO_COMPARE = SUM('NO-SSC HR Trended Turnover'[Voluntary Terminations])
RETURN
    IF(
        NOT ISBLANK(__VALUE_TO_COMPARE),
        DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE) +1
    )
 
The table 'HR Trended Turnover' is related to the table 'Kalender'
calendar table related.png
 
 
 
 
 
 
 
 
 
 
 
 
Turnover can be displayed like this using my quick measure:
turnover.png
 
 
 
However, I am just not able to create a measure for a six month rolling average.
 
I have created a measure for called 'MOV AVG Turnover Prosent':
 
MOV AVG Turnover Prosent =
IF(
    ISFILTERED('Kalender'[Date]),
    ERROR("Rask oppretting av mål for tidsintelligens kan bare grupperes eller filtreres etter datohierarkiet eller den primære datokolonnen levert av Power BI."),
    VAR __LAST_DATE = ENDOFQUARTER('Kalender'[Date].[Date])
    VAR __DATE_PERIOD =
        DATESBETWEEN(
            'Kalender'[Date].[Date],
            STARTOFQUARTER(DATEADD(__LAST_DATE, -2, QUARTER)),
            __LAST_DATE
        )
    RETURN
        AVERAGEX(
            CALCULATETABLE(
                SUMMARIZE(
                    VALUES('Kalender'),
                    'Kalender'[Date].[År],
                    'Kalender'[Date].[QuarterNo],
                    'Kalender'[Date].[Kvartal]
                ),
                __DATE_PERIOD
            ),
            CALCULATE(
                [Total turnover i prosent],
                ALL(
                    'Kalender'[Date].[MonthNo],
                    'Kalender'[Date].[Måned],
                    'Kalender'[Date].[Dag]
                )
            )
        )
)
 
It doesn't work with the date hiearchy (selecting Month) from my related table 'Kalender'
I have tried to make the table 'Kalender' a date table. Didn't help, so I undid it.
moving average displayed.png
 
 
 
 
 
If I change the date format to Date instead of the date hierarchy, I get this error:
(in English, '...quick measure for time intelligence kan only be grouped or filtered by date hierarchy or the primary date column delivered by Power BI)
error mov avg.png
 
 
 
 
 
 
 
 
 
 
I have not found a solution to this problem by formatting my table 'Kalender' as a date table. Neither can I use the calender in the 'HR Trended Turnover' table.
 
If anyone are able to help me find the solution it will be highly appreaciated 🙂
 
Regards,
André
1 ACCEPTED SOLUTION

Hi!

your solution worked out prefectly, even with my filters!

Thank you very much. Appreciate your help!

View solution in original post

4 REPLIES 4
V-lianl-msft
Community Support
Community Support

Hi ,

 

You may have a look at below posts. 

https://community.powerbi.com/t5/Desktop/Moving-Average-for-Prev-6-Next-6-Months/td-p/171712

https://community.powerbi.com/t5/Desktop/Averages-For-The-Last-12-Months/td-p/324289

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

Sample data and expected output will be helpful to provide an accurate solution.

 

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

 

Hi Liang, thank you for your suggestions. I have read and have still not been able to create a moving average for turnover.

Perhaps it is obvious to you which mistakes I am making if I share my files? 

 

moving average_II.png 

 

 

PBIX file:

https://drive.google.com/file/d/1_dv7UJzMxHmbef5AE3SGwfj_2WFUn6ov/view?usp=sharing

Data file:

https://drive.google.com/file/d/12p4z2cScRyYezVbwovJ9F3VXroBFq1TL/view?usp=sharing

 

 

 

Thank you,

BR

André

Try:

Measure = 


CALCULATE(
    AVERAGEX(VALUES(Kalender[Month-Year]),[Total turnover precentage]),
    FILTER(ALL(Kalender),
        COUNTROWS(
            FILTER(Kalender,
            Kalender[month year]>=EARLIER(Kalender[month year])
            )
        )
    ),DATESINPERIOD(Kalender[Date],TODAY(),-6,MONTH)
)

V-lianl-msft_0-1620035630708.png

 

Hi!

your solution worked out prefectly, even with my filters!

Thank you very much. Appreciate your help!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors