cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WBscooby
Helper I
Helper I

Conditional Formatting in matrix based on custom fiscal quarter

Hi

 

I could really do with some help understanding conditional formatting, specifically when using customer fiscal quarters. Our financial year runs form April 1st to Mar 31st so Q1 is Apr-Jun, Q2 Jul-Sep etc. I have created a date calendar with these fiscal quarters.

 

I have created a simple matrix which counts the number of customer orders per customer per quarter. I would like to use conditional formatting using a simple RAG system to show the change each quarter as per the Excel example below. I can't work out how to do this!

 

Any help would be very much appreciated!

 

Thank you 

 

Capture.PNG

1 ACCEPTED SOLUTION

@WBscooby , have measures like

 

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

 

or

 

This Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

 

if([This Qtr] >[Last Qtr], "Green", "Red" )

 

Use in conditional formatting using field value option

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@WBscooby , what is issue, Qtr no? or use of Qtr in conditional

 

You can create a color measure where you use both measure and dimension value

 

examples

 

Color sales = if(AVERAGE(Sales[Sales Amount])<170,"green","red")
Color Year = if(FIRSTNONBLANK('Table'[Year],2014) <=2016,"lightgreen",if(FIRSTNONBLANK('Table'[Year],2014)>2018,"red","yellow"))

Color = if(FIRSTNONBLANK('Table'[Year],2014) <=2016 && AVERAGE(Sales[Sales Amount])<170
,"lightgreen",if(FIRSTNONBLANK('Table'[Year],2014)>2018,"red","yellow"))
Color sales = if([Sales Today] -[sales yesterday]>0,"green","red")

color =
switch ( true(),
FIRSTNONBLANK('Table'[commodity],"NA") ="commodity1" && sum('Table'[Value]) >500,"lightgreen",
FIRSTNONBLANK('Table'[commodity],"NA") ="commodity2" && sum('Table'[Value]) >1000,"lightgreen",
// Add more conditions
"red"
)

switch(true() ,
max(Table[status]) = "Completed" , "Green",
max(Table[status]) = "Running" , "Grey"
max(Table[status]) = "Errored" , "Red"
)

 

How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Hi

 

Thank you for replying. The problem i have is the Qtr - it is custom.

 

I am trying to use conditional formatting to show the change between quarters e.g.

 

If Qtr 2 is greater than Q1 then highlight green

If Qtr 2 is same as Qtr1 then highlight orange

etc

 

I know there is a previousquarter function but this won't work as my quarters are custom.

 

Hope this makes sense!

@WBscooby , have measures like

 

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

 

or

 

This Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

 

if([This Qtr] >[Last Qtr], "Green", "Red" )

 

Use in conditional formatting using field value option

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

Hi

 

Sorry, I seem to be having one last problem - where there is no data for the quarter, the max does not seem to be calculating correctly. The Dax I have used is as follows:

 

ThisQtr = if(
ISBLANK(
CALCULATE(DISTINCTCOUNT(Outcomes[OutcomeID]),FILTER(ALL(Quarters),Quarters[Index]=MAX(Quarters[Index])))
),0,
CALCULATE(DISTINCTCOUNT(Outcomes[OutcomeID]),FILTER(ALL(Quarters),Quarters[Index]=MAX(Quarters[Index])))
)

 

LastQtr = if(
ISBLANK(
CALCULATE(DISTINCTCOUNT(Outcomes[OutcomeID]),FILTER(ALL(Quarters),Quarters[Index]=MAX(Quarters[Index])-1))
),0,
CALCULATE(DISTINCTCOUNT(Outcomes[OutcomeID]),FILTER(ALL(Quarters),Quarters[Index]=MAX(Quarters[Index])-1))
)
 
I added isblank to force 0 values. The calculation in ThisQtr is correct but the LastQtr is not working as expected. Table below to show this:
 
WBscooby_0-1634047194075.png

 

 I can't work out why it's not working - any help would be appreciated. Thank you
 
 

Thank you!

 

I used the formula below (amended slightly for my data) and it worked brilliantly!!

 

This Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

 

if([This Qtr] >[Last Qtr], "Green", "Red" )

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

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!

Top Solution Authors
Top Kudoed Authors