%Turnover over a Range of Months

[PowerAuto82]

Hi,

I currently have the following DAX Calulcations Termination and Headcount to work out the % Turnover

Terminations =

CALCULATE(
COUNT('Fact'[Emp Id]),
USERELATIONSHIP('Fact'[Employment End Date],Dates[Date]),
NOT(ISBLANK('Fact'[Employment End Date])),
'Fact'[Employment End Date] <= TODAY()

)+0



Headcount =
CALCULATE(
COUNTX(
FILTER('Fact','Fact'[Employment Start Date]<=MAX('Dates'[Date])
&&
(ISBLANK('Fact'[Employment End Date])
|| 'Fact'[Employment End Date]>MAX(Dates[Date]))),
'Fact'[Personnel Number]),ALL('Termination Category'[Termination Category])
) + 0

% Turnover = 
    VAR _Headcount =
	
CALCULATE(
    COUNTX(
        FILTER('Fact','Fact'[Employment Start Date]<=MAX('Dates'[Date]) 
        &&
        (ISBLANK('Fact'[Employment End Date]) 
        || 'Fact'[Employment End Date]>MAX(Dates[Date]))),
        'Fact'[Personnel Number]),ALL('Termination Category'[Termination Category])
        ) + 0


	VAR _AvgHeadcount =

AVERAGEX(
       VALUES(Dates[Date]),
        [Headcount]
        ) + 0


	VAR _Turnover =

DIVIDE('Calculations'[Terminations],_AvgHeadcount,BLANK())

RETURN
	_Turnover

But if the end user selects the Year = 2022 and Month = July I want to see the % Turnover on average over the previous 12 months i.e 01/08/2022 to 31/07/2022

other examples of date range to return if they select the highlighted Month Year dates

June 2022 (should display turnover from 01/07/2021 to 30/06/2022)

May 2022 (should display turnover from 01/06/2021 to 31/05/2022)

April 2022 (should display turnover from 01/05/2021 to 30/04/2022)

Thanks

[PowerAuto82]

Hi @v-yangliu-msft Thanks for your reply, just look at your example and not sure if it is working because when selecting the different Month or Years the adjacent table would not show the dates within that date range, all dates are showing

[v-yangliu-msft]

Hi  @PowerAuto82 ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Date =
DISTINCT('Table1'[Date])

Create caluculated column.

Year = YEAR('Date'[Date])

Month = MONTH('Date'[Date])

2. Create measure.

Flag =
var _year=SELECTEDVALUE('Date'[Year])
var _month=SELECTEDVALUE('Date'[Month])
var _start=
    DATE(
        _year,_month-11,1)
var _end=
EOMONTH(
    DATE(
        _year,_month,1),0)
return
AVERAGEX(FILTER(ALL(Table1),
'Table1'[Date]>=_start&&'Table1'[Date]<=_end),[Sales])

3. Result:

If you need pbix, please click here.

%Turnover over a Range of Months.pbix

If this method can't meet your requirement, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you

Best Regards,

Liu Yang

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