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

[AndreBV]

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. 

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'

Turnover can be displayed like this using my quick measure:

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.

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)

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é

[V-lianl-msft]

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.

[AndreBV]

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? 

 

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é

[V-lianl-msft]

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)
)

[AndreBV · Accepted solution]

Hi!

your solution worked out prefectly, even with my filters!

Thank you very much. Appreciate your help!