cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Dynamic visual indicator for workdays - Cumulative sum per month

Hello!

I've the following bar chart:
Capture.PNG
As you can see, the first 9 days of the month are in BLUE where as the rest of the month is in RED. My objective is to make it appear BLUE always for the first 9 WORKDAYS of the selected month. IE: for March 2021, it would paint un BLUE until 03/11/2021 because that's the 9th workday of the month.

 

I created a calendar table where I already have a column that registers the workdays with 1 and weekends with 0. I thought maybe I could make a cumulative sum of the workdays and then use that as a function in the "Data Color" option for the chart. but I can't make it work so It sums it by month.

 

cumulative.PNG

 

I'm open to new ideas to make this work 🙂

 

Thank you so much for your time!

3 REPLIES 3
amitchandak
Super User
Super User

@Anonymous , if you have axis(no legend ) and one measure you can do conditional formatting  using Color measure in Data Color

if(day(max(date[date])) <=9 ,"Blue", "red") 

 

// for work day refer

new columns in date table

Work Day = if(WEEKDAY([Date],2)>=6,0,1)
Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
Work Date cont Rank = RANKX(ALL('Date'),[Work Date Cont],,ASC,Dense)

 

Measure

MTD Work Day= CALCULATE(max(Date[Work Date cont Rank]),DATESMTD('Date'[Date]))

 

if([MTD Work Day] <=9 ,"Blue", "red") 

 

refer my video on this : https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L&index=14

 

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
Anonymous
Not applicable

Hey there!

I Tried adding your columns and measures in a table view and ended up getting this:

cumulative.PNG

 

It is not calculating properly the first 9 workdays. The "Work Date con Rank" needs to reset after every month. Also, by applying this solution it seems like the Measure to designate the color "Red" or "Blue" would include weekends IF they're at the end of the 9th day counter, for example, if the 9th work day ends up being a friday, the formula would count the next saturday and Sunday as "Red" since the counter remains at 9 for those 2 days.

Sorry to bother you, do you think that this is achievable? If it's not, I'm open to suggestions!

Thank you so much for your time,





Thank you for your time!!

Hi @Anonymous ,

 

Please provide sample data for easy copying or a sample .pbix file.

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors