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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ryan_b_fiting
Post Patron
Post Patron

Conditional Formatting for Dates

Hello Community - 

 

I am looking to conditionally format a column chart that shows quarterly Sales Data.  What I want to format is the current quarter on the chart and the same quarter last year.  

For example, there is a quarterly date slicer that DOES NOT interact with the chart, so if I select Q1 2022, this chart still shows all the quarterly sales numbers, but I want the columns of Q1 2022 and Q1 2021 to be formatted a different color so they are quickly identified in the trend.  See below screen shot.

Qtrly Sales.PNG

The data is very basic with a sales table and a date tables 

 

Sales = SUMX(
       Sales_Date,[Sales Dollars])

Sales LY = CALCULATE(
      [Sales],
        SAMEPERIODLASTYEAR(Date_Table,[Date])
)

 

 

I cannot figure out the DAX to get this conditional formatting to work for the chart and highlight the selected quarter and LY Quarter.

Any help is greatly appreciated, as always.

Ryan F

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@ryan_b_fiting not sure how you did it, what your updated measure looks like? It should work.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

11 REPLIES 11
parry2k
Super User
Super User

@ryan_b_fiting instead of SELECTEDVALUE use MAX

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Awesome, this works!

Thanks for the tips and insight @parry2k 

parry2k
Super User
Super User

@ryan_b_fiting not sure how you did it, what your updated measure looks like? It should work.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k I created a disconnected date table with Date, YearQtr in it (Table is the disconnected table in the measures below).  I used YearQtr as my slicer.

I wrote the following measures

previous YearQuarter = 
CONCATENATE(
    YEAR(SELECTEDVALUE('Table'[Date])) - 1, 
    CONCATENATE(
        " ", 
        CONCATENATE(
            "Q", QUARTER(SELECTEDVALUE('Table'[Date]))
        )
    )
)

current YearQuarter = 
CONCATENATE(
    YEAR(SELECTEDVALUE('Table'[Date])), 
    CONCATENATE(
        " ", 
        CONCATENATE(
            "Q", QUARTER(SELECTEDVALUE('Table'[Date]))
        )
    )
)

quarter_based_color = // measure for dynamic colors for bars
var quarterYear = SELECTEDVALUE('Table'[YearQtr])
RETURN
SWITCH(
    TRUE(),
    quarterYear = [current YearQuarter], "#ac1b4c", // hex code for red shade for current year-quarter
    quarterYear = [previous YearQuarter], "#ac1b4c", // hex code for red shade for previous year-quarter
    "#00a0ad" //hex code to represent rest of year-quarters on the chart
)

  I used the quarterbased color as my conditional formatting.

On my chart, I have Total sales by Quarter (Date Table that is connected to sales table).

 

I am doing something wrong in the first 2 measures but I am not sure what.  The output from the measure is just a Q.

parry2k
Super User
Super User

@ryan_b_fiting Just my 2 cents in here. What @Pragati11  suggested is correct, to make the slicer work, you need to create a disconnected table for the slicer which will not filter your visuals, and based on the selected value from the slicer, you can recreate the measures that @Pragati11  provided and it will work.

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks for your responding here @parry2k 🙂

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Hey @parry2k I am trying to wrap my head around how a disconnected table would work here.

I have created that disconnected date table, used that as my slicer and rewrote the first two measures suggested by @Pragati11 .

I am still not getting the desired result.  Now ALL of the columns get formatted as red no matter what Quarter I select from the slicer.

 

Any insight is appreciated!

Thanks

Ryan

Pragati11
Super User
Super User

Hi @ryan_b_fiting ,

 

You can achieve this by using a measure trick along with dynamic custom measure in Power BI.

Consider the following sample data:

Pragati11_0-1649177851529.png

I have representd this information on a clustered column chart as shown below:

As the current quarter for this year is Q2 2022, We need a different color for bars for Q2 2022 and Q1 2022 (same quarter previous year)

Pragati11_1-1649177944828.png

Firstly, I created 2 measure to get current year-quarter and previous year-quarter values:

current YearQuarter = 
CONCATENATE(
    YEAR(TODAY()), 
    CONCATENATE(
        " ", 
        CONCATENATE(
            "Q", QUARTER(TODAY())
        )
    )
)
previous YearQuarter = 
CONCATENATE(
    YEAR(TODAY()) - 1, 
    CONCATENATE(
        " ", 
        CONCATENATE(
            "Q", QUARTER(TODAY())
        )
    )
)

On top of these 2 measures, I created a third measure to give dynamic colors to the bars for Q2 2022 and Q2 2021:

quarter_based_color = // measure for dynamic colors for bars
var quarterYear = SELECTEDVALUE(QuarterYearSales[Year_Quarter])
RETURN
SWITCH(
    TRUE(),
    quarterYear = [current YearQuarter], "#ac1b4c", // hex code for red shade for current year-quarter
    quarterYear = [previous YearQuarter], "#ac1b4c", // hex code for red shade for previous year-quarter
    "#00a0ad" //hex code to represent rest of year-quarters on the chart
)

Now in the clusterted column chart --> format options --> data colors --> click on function icon --> new window opens:

Select the format style as field value

Select the field as quarter_based_color measure

Pragati11_3-1649178385848.png

We get the required chart. 🙂

Pragati11_4-1649178450834.png

The chart highlights required bars based on the required:

  • 2022 Q2 (current year, current quarter)
  • 2021 Q2 (previous year, current year-quarter)

Hope the solution helps.

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

@Pragati11  Thanks for the reply, I think this almost gets me there.

I do not need the dates to based on TODAY() only.  I need to be able to slice the quarters and have the conditional formatting change based on a quarter selected.  For example if I select Q1 2021, I would expect the bars for Q1 2021 and Q1 2020 to be colored as red.

 

Is there a small tweak that can be made to adjust the first 2 measures to account for that?

 

Thanks

Ryan

Hi @ryan_b_fiting ,

 

I don't think you can logically do that. 

 

the custom color formatting is based on an action. If you create a slicer just taking selected year-quarter and disable it's action on your visual. But further you want to apply the conditional formatting to color the bars based on the selection on the slicer, but that custom measure to be used on the visual that considers the slicer selection. The visual won't change the bar colors based on the measure as the interaction is off on the visual against the slicer selected.

Without enabling the interaction on visual against the year-quarter slicer, how will the visual judge what value is selected in the slicer and how to color the bars based on it. So you will have to pass the slicer selection value somehow to the visual to make the conditional formatting work based on the slicer selection.

 

I hope you get the explanation above. Let me know if that is not clear.

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

@Pragati11 your reply makes total sense.   I was not sure there was actually a way to do this, but I figured if anyone knew, it would be someone in the community board.  Thanks for taking the time to look and the post and provide your feedback!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.