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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tbucki1
Frequent 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
Frequent 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
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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