Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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....**
Thoughts? Thanks for your help.
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |