Assigning Monthly Data to Daily and Latest Value

[swwong1]

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.

https://www.dropbox.com/scl/fi/h6v55uirh6yjyhsg4ufwy/LatestHeadcount.pbix?rlkey=k72ii6hk0p1c6th7v5bl... 

Thanks All!

[Wilson_ · Accepted 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.