cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I

## Last_non_blank value of employee count (Fill down)

Hi all,

I need to fill down the unique count of employees.

I have this table and i basically need it to give me the last available value.

I need this:

This is my measure for counting employees:

#Employees =
CALCULATE(
DISTINCTCOUNT('DB Salary PaySlips'[employee_ID]),
FILTER(
'GS Medarbejder Liste',
'GS Medarbejder Liste'[Stilling] <> "Piccoline"
)
) + 0.5 * CALCULATE(
DISTINCTCOUNT('DB Salary PaySlips'[employee_ID]),
FILTER(
'GS Medarbejder Liste',
'GS Medarbejder Liste'[Stilling] = "studentermedhjælper"
)
)

and i have a date table

I tried a lot of different measures.

like this: but as you can see it dont work.

#Employees (Fill Down) =
VAR EmployeeCount =
CALCULATE(
DISTINCTCOUNT('DB Salary PaySlips'[employee_ID]),
FILTER(
'GS Medarbejder Liste',
'GS Medarbejder Liste'[Stilling] <> "Piccoline"
)
) + 0.5 * CALCULATE(
DISTINCTCOUNT('DB Salary PaySlips'[employee_ID]),
FILTER(
'GS Medarbejder Liste',
'GS Medarbejder Liste'[Stilling] = "studentermedhjælper"
)
)
RETURN
LASTNONBLANKVALUE('DB Salary PaySlips'[start_date], EmployeeCount)

1 ACCEPTED SOLUTION
Helper I

I managed to solve it using a combination of my limited DAX knowledge and consulting with ChatGPT:

Last non-blank count of #employees used for budget calc. =
VAR Last_Date = CALCULATE(MAX('DB Salary PaySlips'[start_date]),
FILTER(ALL('_dimDate'[Date]),'_dimDate'[Date] <= MAX('_dimDate'[Date])))
VAR Employee_Count = CALCULATE(
DISTINCTCOUNT('DB Salary PaySlips'[employee_ID]),
FILTER(
ALL('DB Salary PaySlips'),
'DB Salary PaySlips'[start_date] = Last_Date
),
FILTER(
'GS Medarbejder Liste',
'GS Medarbejder Liste'[Stilling] <> "Piccoline"
)
)
VAR Student_Count = CALCULATE(
DISTINCTCOUNT('DB Salary PaySlips'[employee_ID]),
FILTER(
'GS Medarbejder Liste',
'GS Medarbejder Liste'[Stilling] = "studentermedhjælper"
)
, ALL('DB Salary PaySlips'),
'DB Salary PaySlips'[start_date] = Last_Date
) * 0.5
RETURN  Student_Count + Employee_Count
6 REPLIES 6
Helper I

I managed to solve it using a combination of my limited DAX knowledge and consulting with ChatGPT:

Last non-blank count of #employees used for budget calc. =
VAR Last_Date = CALCULATE(MAX('DB Salary PaySlips'[start_date]),
FILTER(ALL('_dimDate'[Date]),'_dimDate'[Date] <= MAX('_dimDate'[Date])))
VAR Employee_Count = CALCULATE(
DISTINCTCOUNT('DB Salary PaySlips'[employee_ID]),
FILTER(
ALL('DB Salary PaySlips'),
'DB Salary PaySlips'[start_date] = Last_Date
),
FILTER(
'GS Medarbejder Liste',
'GS Medarbejder Liste'[Stilling] <> "Piccoline"
)
)
VAR Student_Count = CALCULATE(
DISTINCTCOUNT('DB Salary PaySlips'[employee_ID]),
FILTER(
'GS Medarbejder Liste',
'GS Medarbejder Liste'[Stilling] = "studentermedhjælper"
)
, ALL('DB Salary PaySlips'),
'DB Salary PaySlips'[start_date] = Last_Date
) * 0.5
RETURN  Student_Count + Employee_Count
Solution Supplier

Hi @Jakob_BI ,

You can try the below measure with adding Removefilters(Month) :

#Employees =
Var a = CALCULATE(
DISTINCTCOUNT('DB Salary PaySlips'[employee_ID]),
FILTER(
'GS Medarbejder Liste',
'GS Medarbejder Liste'[Stilling] <> "Piccoline"
)
) + 0.5 * CALCULATE(
DISTINCTCOUNT('DB Salary PaySlips'[employee_ID]),
FILTER(
'GS Medarbejder Liste',
'GS Medarbejder Liste'[Stilling] = "studentermedhjælper"
)
)
return calculate(a,removefilters(Month))

Hope this helps!

Thanks,
Avantika Thakur!
Helper I

same result unfortunately

Solution Supplier

Hi @Jakob_BI ,

Can you also try the below measure if it works?

CALCULATE(CALCULATE(
DISTINCTCOUNT('DB Salary PaySlips'[employee_ID]),
FILTER(
'GS Medarbejder Liste',
'GS Medarbejder Liste'[Stilling] <> "Piccoline"
)
) + 0.5 * CALCULATE(
DISTINCTCOUNT('DB Salary PaySlips'[employee_ID]),
FILTER(
'GS Medarbejder Liste',
'GS Medarbejder Liste'[Stilling] = "studentermedhjælper"
)
),REMOVEFILTERS(_dimdate[Month]))
Helper I

maybe it is possible to create a new table that i can use? that just count unique employees per month (using the measure i have) and then populating that table with data for months that have not happened yet?

like this:

Helper I

thanks for trying but same result:

i will try provide as much info to help you understand my (as i am very interested in solving this).