cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Dynamic visual indicator for workdays - Cumulative sum per month

Hello!

I've the following bar chart:

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.

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

Thank you so much for your time!

3 REPLIES 3
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

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:

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,

Community Support

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.

Announcements