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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
swwong1
Helper III
Helper III

Assigning Monthly Data to Daily and Latest Value

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.

 

swwong1_0-1712936381251.png

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

 

Thanks All!

 

1 ACCEPTED SOLUTION
Wilson_
Memorable Member
Memorable Member

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):

 

Wilson__0-1713152008431.png

 

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.

View solution in original post

1 REPLY 1
Wilson_
Memorable Member
Memorable Member

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):

 

Wilson__0-1713152008431.png

 

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.