Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi All
I would like to calculate the total sales/headcount (employee) but headcount figures are only updated once at month end. Is there a way to 'force' this value into daily without having to change the data structure of the headcount table?
Therefore, I would like to use 12 for everyday in January to calculate average sales per employee.
However for April, I would like continue to take 16 as 31 March is the latest available headcount data I have. Once I have the month-end figures for April, will then use the figure for April.
Thanks All!
Solved! Go to Solution.
Hello swwong1,
Thank you for providing a pbix file. Try this in a new measure:
Monthly Headcount =
-- calculates headcount for the month
VAR MonthEnd = EOMONTH ( SELECTEDVALUE ( dDate[Date] ), 0 )
VAR MonthlyHeadcount =
CALCULATE (
SUM ( fHeadcount[Headcount] ),
REMOVEFILTERS ( dDate ),
dDate[Date] = MonthEnd
)
-- calculates last available headcount
VAR LatestHeadCountDate =
LASTNONBLANK (
ALL ( ddate[Date] ),
CALCULATE ( SUM ( fHeadcount[Headcount] ) )
)
VAR LatestHeadCount =
CALCULATE (
SUM ( fHeadcount[Headcount] ),
REMOVEFILTERS ( dDate ),
dDate[Date] = LatestHeadCountDate
)
-- returns headcount for the mopnth if it exists, and last available headcount otherwise
VAR Result = COALESCE ( MonthlyHeadcount, LatestHeadCount )
RETURN Result
This is what I have in a visual with your Total Sales and this Monthly Headcount measure (I've filtered out all blank Total Sales):
Let me know if that's not what you were looking for. 😄
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.
Hello swwong1,
Thank you for providing a pbix file. Try this in a new measure:
Monthly Headcount =
-- calculates headcount for the month
VAR MonthEnd = EOMONTH ( SELECTEDVALUE ( dDate[Date] ), 0 )
VAR MonthlyHeadcount =
CALCULATE (
SUM ( fHeadcount[Headcount] ),
REMOVEFILTERS ( dDate ),
dDate[Date] = MonthEnd
)
-- calculates last available headcount
VAR LatestHeadCountDate =
LASTNONBLANK (
ALL ( ddate[Date] ),
CALCULATE ( SUM ( fHeadcount[Headcount] ) )
)
VAR LatestHeadCount =
CALCULATE (
SUM ( fHeadcount[Headcount] ),
REMOVEFILTERS ( dDate ),
dDate[Date] = LatestHeadCountDate
)
-- returns headcount for the mopnth if it exists, and last available headcount otherwise
VAR Result = COALESCE ( MonthlyHeadcount, LatestHeadCount )
RETURN Result
This is what I have in a visual with your Total Sales and this Monthly Headcount measure (I've filtered out all blank Total Sales):
Let me know if that's not what you were looking for. 😄
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
80 | |
60 | |
59 | |
58 |
User | Count |
---|---|
157 | |
118 | |
101 | |
76 | |
68 |