cancel
Showing results for
Did you mean:
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

1 ACCEPTED SOLUTION
Super User

@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

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!
5 REPLIES 5
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",
"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

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!
Helper I

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!

Super User

@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

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!
Helper I

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

Helper I

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

Announcements