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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
DH3612
Frequent Visitor

Average Count

Hi folks. People Have been amazing so far helping me understand what to do. Hoping we can get this next bit running ok. 

 

I work out Current EMployees using this piece of code which works great:

 

Current Employees = CALCULATE(COUNTx(FILTER(STAFFMASTER,STAFFMASTER[Current Employment: Start Date]<=max('Date'[Date]) && (ISBLANK(staffmaster[End Date]) || staffmaster[End Date]>max('Date'[Date]))),(STAFFMASTER[Unique Id])),CROSSFILTER(STAFFMASTER[Current Employment: Start Date],'Date'[Date],None))

 Then I work out Employees at a specific date using this piece:

Employed Staff Count = 
 var minD = MIN('Date'[Date])
  var maxD = MAX('Date'[Date])
  RETURN CALCULATE(
    DISTINCTCOUNT('STAFFMASTER'[Unique ID]),
    'STAFFMASTER'[Current Employment: Start Date] <= minD &&
    ( 'STAFFMASTER'[End Date] >= maxD || ISBLANK('STAFFMASTER'[End Date]) ),
    CROSSFILTER('Date'[Date], STAFFMASTER[Current Employment: Start Date], None)
  )

 

If I try to do a simple measure for example of 

Average Staff = ([current employees]+[Employed Staff Count])/2 

then I get a wrong answer because the measure doesnt have the same filters active as the measures in those code snippets. 

 

If I then try and resolve this by doing a measure like this - 

Average Staff = 
VAR CurrentEmps = CALCULATE(COUNTx(FILTER(STAFFMASTER,STAFFMASTER[Current Employment: Start Date]<=max('Date'[Date]) && (ISBLANK(staffmaster[End Date]) || staffmaster[End Date]>max('Date'[Date]))),(STAFFMASTER[Unique Id])),CROSSFILTER(STAFFMASTER[Current Employment: Start Date],'Date'[Date],None))
VAR Employed = 
 var minD = MIN('Date'[Date])
  var maxD = MAX('Date'[Date])
  RETURN CALCULATE(
    DISTINCTCOUNT('STAFFMASTER'[Unique ID]),
    'STAFFMASTER'[Current Employment: Start Date] <= minD &&
    ( 'STAFFMASTER'[End Date] >= maxD || ISBLANK('STAFFMASTER'[End Date]) ),
    CROSSFILTER('Date'[Date], STAFFMASTER[Current Employment: Start Date], None)
  )

RETURN
    (CurrentEmps + Employed) / 2

I get an incorrect answer. 

 

Any suggestions how to work this or even a simpler solution. 

 

Basically the sum needs to be current staff+staff at a specific date / 2 which will give the average staff over that period. 

2 REPLIES 2
DH3612
Frequent Visitor

Thanks,

 

I amended to use the code you provided, but get the same answer.

 

in the example I am using, I have the date filtered to 31/10/22, and the staff count there is 1789 (Employed Staff Count). The staff count today is 1996 (Current Staff). So adding those together then dividing by two should be 1892.5. But the result seems to be 1788. 

fbura
New Member

Hi, without investigating much about the logics about the calculation of 'Employed' and 'CurrentEmps' i assume your DAX formula will result not in an incorrect answer but in an error due to the fact you put two RETURN statement inside one DAX formula. Before thinking about the quality of your calculation i would try to run this instead:

 

VAR CurrentEmps =
CALCULATE (
COUNTX (
FILTER (
STAFFMASTER,
STAFFMASTER[Current Employment: Start Date] <= MAX ( 'Date'[Date] )
&& (
ISBLANK ( staffmaster[End Date] )
|| staffmaster[End Date] > MAX ( 'Date'[Date] )
)
),
( STAFFMASTER[Unique Id] )
),
CROSSFILTER ( STAFFMASTER[Current Employment: Start Date], 'Date'[Date], NONE )
)
VAR Employed =
CALCULATE (
DISTINCTCOUNT ( 'STAFFMASTER'[Unique ID] ),
'STAFFMASTER'[Current Employment: Start Date] <= MIN ( 'Date'[Date] )
&& (
'STAFFMASTER'[End Date] >= MAX ( 'Date'[Date] )
|| ISBLANK ( 'STAFFMASTER'[End Date] )
),
CROSSFILTER ( 'Date'[Date], STAFFMASTER[Current Employment: Start Date], NONE )
)
RETURN
( CurrentEmps + Employed ) / 2

 

Logics are the same as yours but in my opinion minD and maxD variables were not necessary.

 

Hope this will help you! Cheers!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors