Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Jakob_BI
Helper I
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. 

Jakob_BI_0-1679844260113.png

I need this:

Jakob_BI_2-1679844402223.png

 

 

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 
Jakob_BI_1-1679844314378.png

 

 

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)

Jakob_BI_3-1679844831534.png

 

 

1 ACCEPTED SOLUTION
Jakob_BI
Helper I
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

View solution in original post

6 REPLIES 6
Jakob_BI
Helper I
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
Avantika-Thakur
Solution Supplier
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!

same result unfortunately

Jakob_BI_0-1679852366725.png

 

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]))

@Avantika-Thakur  

 

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:

Jakob_BI_0-1679985643505.png

 

thanks for trying but same result:

Jakob_BI_0-1679862271619.png

 

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

Jakob_BI_1-1679862459482.png

 

Jakob_BI_2-1679865038563.pngJakob_BI_3-1679865068781.png

 



Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.