Forum Discussion
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
- amitchandak
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-valuesHey 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,
Thank you for your time!!
v-lionel-msftCommunity Support
Hi Anonymous ,
Please provide sample data for easy copying or a sample .pbix file.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
