Conditional Formatting in matrix based on custom fiscal quarter

[WBscooby]

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 

[amitchandak]

@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

[WBscooby]

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!

[amitchandak · 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

[WBscooby]

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:

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

[WBscooby]

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" )