Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
What I want to see in the 2nd column of my matrix visual: how many employees are there in the next month and later on which employees are new or let.
I want to do this by comparing the population of employees (this month vs next month) because I want to make this visual by department. Some employees change department and I want to see who is new/left in the department.
The first step I try is to count the rows of the next month but I get a blank result. (see example below: When you click on the month you see the employees in detail)
You can see the used measure below. When I use the thismonth value to return , return COUNTROWS(distinct(EmployeesOnFirstDateThisMonth)), I get the same value as in the 1st column so that works fine.
I have used an all filter but that doesn't work.
So what am I doing wrong?
My measure:
EmployeesNextMonth =
VAR Firstdaythismonth = FIRSTDATE('MyOwndatetable'[date])
VAR Firstdaynextmonth = FIRSTDATE(NEXTMONTH('MyOwndatetable'[date]))
VAR EmployeesOnFirstDateThisMonth =
CALCULATETABLE(VALUES('EmployeeTable'[Employee]),
FILTER(VALUES('EmployeeTable'[date]),'EmployeeTable'[date] = Firstdaythismonth)
)
VAR EmployeesOnFirstDateNextMonth =
CALCULATETABLE(VALUES('EmployeeTable'[Employee]),
FILTER(VALUES('EmployeeTable'[date]),'EmployeeTable'[date] = Firstdaynextmonth)
)
return COUNTROWS(distinct(EmployeesOnFirstDateNextMonth))
--return COUNTROWS(distinct(EmployeesOnFirstDateThisMonth)) THIS RETURNS THE CORRECT VALUE
Solved! Go to Solution.
This is my solution after trying again and again:
CALCULATETABLE(VALUES('EmployeeTable'[Employee]),
DATESBETWEEN('MyOwndatetable'[date],Firstdaynextmonth,Firstdaynextmonth)
thanks for the suggested solution
This is my solution after trying again and again:
CALCULATETABLE(VALUES('EmployeeTable'[Employee]),
DATESBETWEEN('MyOwndatetable'[date],Firstdaynextmonth,Firstdaynextmonth)
thanks for the suggested solution
Hi @CBO2404 ,
I created some data:
Here are the steps you can follow:
1. Create calculated column.
YEAR_MONTH = YEAR('Table'[date])&""&MONTH('Table'[date])
Index = RANKX('Table',[date],,ASC,Dense)
Status =
var _lastdeport=CALCULATE(MAX('Table'[deport]),FILTER('Table',[ID]=EARLIER('Table'[ID])&&[Index]=EARLIER('Table'[Index])-1))
return
IF(
[deport]<>_lastdeport,IF([Index]=1,"Old","New"),"Old")
2. Create measure.
Flag =
IF(
ISINSCOPE('Table'[ID]),
MIN('Table'[Status]),CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALL('Table'),'Table'[YEAR_MONTH]=MAX('Table'[YEAR_MONTH]))))
3. Result:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |