cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nganbla
Helper II
Helper II

give a staff number month over month

Dear all bros,

 

I have the case as below and need your help.

 

Datedepartmenttotal staff
jan 2020sales10
june 2020cs5
may 2020sales8
feb 2021cs4

 

above table show the staff per department: for sales they have 10 staff on jan 2020 and before time, the staff amount unchange untill may 2020 is 8. the same explain for cs department.  

 

so i need the table like

jan 2020 sales 10

feb 2020 sales 10

.....

may 2020 sales 8

march 2021 sales (Still ) 8 

 

I need the table as above to count the order per sales. 

 

any recomend to do like this

 

thank you  for reading and help

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, @nganbla 

Please kindly check if the below picture is what you are looking for.

if not, please let me know.

 

I created a custom-date-table and connected it to the main table.

Each month is pulling the information that is the staff count, and if it is blank, then it looks for the previous nonblank value.

the link to the sample pbix file is down below.

 

lastnonblank sales staff count =
CALCULATE (
LASTNONBLANKVALUE (
Data[Date],
CALCULATE ( SUM ( Data[total staff] ), Data[department] = "sales" )
),
FILTER ( ALLSELECTED ( Data ), Data[Date] <= MAX ( dates[Date] ) )
)
 
lastnonblank cs staff count =
CALCULATE (
LASTNONBLANKVALUE (
Data[Date],
CALCULATE ( SUM ( Data[total staff] ), Data[department] = "cs" )
),
FILTER ( ALLSELECTED ( Data ), Data[Date] <= MAX ( dates[Date] ) )
)
 
Picture1.png

https://www.dropbox.com/s/jr2fva1kzh65018/nganbla%202021%200325.pbix?dl=0 

 

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos!!


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

1 REPLY 1
Jihwan_Kim
Super User
Super User

Hi, @nganbla 

Please kindly check if the below picture is what you are looking for.

if not, please let me know.

 

I created a custom-date-table and connected it to the main table.

Each month is pulling the information that is the staff count, and if it is blank, then it looks for the previous nonblank value.

the link to the sample pbix file is down below.

 

lastnonblank sales staff count =
CALCULATE (
LASTNONBLANKVALUE (
Data[Date],
CALCULATE ( SUM ( Data[total staff] ), Data[department] = "sales" )
),
FILTER ( ALLSELECTED ( Data ), Data[Date] <= MAX ( dates[Date] ) )
)
 
lastnonblank cs staff count =
CALCULATE (
LASTNONBLANKVALUE (
Data[Date],
CALCULATE ( SUM ( Data[total staff] ), Data[department] = "cs" )
),
FILTER ( ALLSELECTED ( Data ), Data[Date] <= MAX ( dates[Date] ) )
)
 
Picture1.png

https://www.dropbox.com/s/jr2fva1kzh65018/nganbla%202021%200325.pbix?dl=0 

 

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos!!


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors