cancel
Showing results for
Did you mean:
Regular Visitor

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

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

2 REPLIES 2
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 (
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

Super User II

Hey @tbucki1 ,

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

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

Announcements