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. 

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é