distinct count for previous x periods withoud date table

[Mihu]

Hi everyone,

I am facing challenge that i need to aggregate data for last few periods but these periods are not date table and there is no any dependence where period start and end so using parallelperiod etc formulas is not possible;D

So here is my sample data:

employee id	period
1	201801
2	201801
3	201801
4	201801
1	201802
3	201802
4	201802
1	201803
3	201803
4	201803
5	201803
1	201804
3	201804
4	201804
5	201804
6	201804

And I need to show it this way in PBI:

period	employees distinct	employees distinct 3 periods
201801	4	4
201802	3	4
201803	4	5
201804	5	5

So employees distinct is a distinct count of employees in a period by their id,

employees distinct 3 periods is a distinct count of employees from last 3 periods.

I tried to combine calculate, filter and lookupvalue to filter data for last three  periods like this:

CALCULATE(DISTINCTCOUNT('Employee by Period'[Employee ID]),FILTER( Period, LOOKUPVALUE(Period[Name],Period[Name],FORMAT(VALUE(Period[Name]) - 2,"######")) || LOOKUPVALUE(Period[Name],Period[Name],FORMAT(VALUE(Period[Name]) - 1,"######")) || VALUE(Period[Name]) ))

But it is still showing data only for 'current period' like 'employees distinct' described above.

If you have any idea how to deal with it i would really appriciate it 🙂

[v-yuta-msft]

@Mihu ,

Isn't the expected table like below? In addtion, I'm still not very clear about "last three  periods" you mentioned, could you please share more details?

period	employees distinct	employees distinct 3 periods
201801	4	4
201802	3	4
201803	4	5
201804	5	5

Regards,

Jimmy Tao

[Mihu]

@v-yuta-msft thank you for answer, yea the result table is expected as you posted, i edited it.

According to your question in example of 201803 period.

For 201803 period we have employees 1,3,4,5

for 201802 period we have employees 1,3,4

and for 201801 period we have employees 1,2,3,4

So unique employees for last 3 periods of 201803 is 1,2,3,4,5 and the sum is 5.

BR,

Mihu

[v-yuta-msft · Accepted solution]

@Mihu ,

You can create two measures using dax below:

employees distinct = COUNT('Table'[employee id])

employees distinct 3 periods = 
VAR Current_Period = MAX('Table'[period])
RETURN
CALCULATE(DISTINCTCOUNT('Table'[employee id]), FILTER(ALL('Table'), 'Table'[period] <= Current_Period && 'Table'[period] >= Current_Period - 2 ))

 

Community Support Team _ Jimmy Tao

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