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
WBscooby
Helper III
Helper III

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

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

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

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
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.