cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tbucki1
Regular Visitor

Headcount FTE report needed

I am building a headcount report from a dataset that shows the actual headcount/FTE for a month. I.e  Jan 2020 = 5, Feb 2020 = 6, Mar 2020 = 10...... Jan 2021 = 8....etc... My problem is when I put this into a pivot my grand total (by quarter or year) shows the sum of all 12 months or my quarter 1 view shows the sum of three months. When I show "total" or group by quarter I want to see the latest value for that time period.

 

To solve for this I created a measure that uses the ClosingBalanceMonth dax formula, however I am now successfully able to see for year end 2019 and 2020 "total" ending headcount for that time period which matches with the december, However for 2021 which isn't done yet (only Jan and Feb) the total is blank and it should show be the february numbers instead... How do I get the subtotals for year or quarter to show me the latest values for that time period?? 2021 Total should show Feb numbers and if I decided to bring in Q1 2021 it ALSO should show me latest headcount for that time period in this case Feb. See screenshot below as an example of what I am trying to do/show....

 

How would you build a measure to give me the 2021 values so it isn't blank like in both views below?

 

 

Showing Monthly headcount numbers by year

Capture.jpg

 

 

 

 

 

 

 

 

Showing monthly numbers grouped by quarter and year. **Note 2021 is the only one not correct....**

Capture2.JPG

 

 

 

 

 

 

 

Thoughts? Thanks for your help.

2 REPLIES 2
tbucki1
Regular Visitor

Hi @TomMartens , thanks for the reply. I did review the link you sent and can see from the examples on that page that the measure I want to build is like the "LastDateByCustomerEver" as referenced about halfway down. However my data is in a fact table stacked tall and unpivoted with three columns; Date, Account, and Values. Hence why I have a measure for FTE currently that is "CALCULATE( SUM([Values]), [Account] = "Headcount") How do I take something I already built (FTE) and plug it into this code below? The below code example (from page link you sent) uses balances as a table to do grouping within the summarize function. Not sure how to convert what I have into this....?? and get the new measure I need which i'll call "LastHeadCountValue".... I do have a dates table that is joined to the Date column on my fact table. Star Schema Diagram is the setup here... Thoughts?

Balance LastDateByCustomerEver :=
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR MaxBalanceDates =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( Balances, Customers[Name] ),
            "@MaxBalanceDate", CALCULATE ( MAX ( Balances[Date] ) )
        ),
        'Date'[Date] <= MaxDate
    )
VAR MaxBalanceDatesWithLineage =
    TREATAS ( MaxBalanceDates, Customers[Name], 'Date'[Date] )
VAR Result =
    CALCULATE ( SUM ( Balances[Balance] ), MaxBalanceDatesWithLineage )
RETURN
    Result

 

TomMartens
Super User II
Super User II

Hey @tbucki1 ,

 

the challenge you are facing has a name, it's called semi-additive measures.

 

This article Semi-additive calculations – DAX Patterns

how you can tackle this challenge.

 

Hopefully, this provides what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors