cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors