Color formatting end-of-quarter months differently from other months

[Anonymous]

I have a bar graph that conditionally color formats each month based on whether that month is meeting a monthly goal of 203 backlog risks processed:

However, the new ask is to format the end-of-quarter months (Mar/Jun/Sep/Dec) to be formatted according to a *quarterly* goal (609) alongside the other months and their monthly goal. Here is an example of what I'm going for:

The measure I use to determine the risk count is the following:

Backlog Processed Monthly % = 
var PrevMonthCount = CALCULATE(DISTINCTCOUNT('risk_table'[risk_id]), PARALLELPERIOD(vw_dimdate[date_dt],-1,MONTH))
var RiskCountLatest =
IF(CALCULATE(DISTINCTCOUNT('risk_table'[risk_id])) = BLANK(), 0,
CALCULATE(DISTINCTCOUNT('risk_table'[risk_id]), risk_table[snapshot_dt] = MAX(risk_table[snapshot_dt])))
RETURN
DIVIDE(
PrevMonthCount - RiskCountLatest
,
CALCULATE(DISTINCTCOUNT('risk_table'[risk_id]), vw_dimdate[date_dt] = DATE(2023, 12, 31))
)

I also made a simple calculated column in my calendar table to differeniate between these EOQ months versus non-EOQ, but it only works on a monthly basis. I am at a loss as to how to make every third month format based on a three-month cumulative goal rather than the monthly goal of the other nine months.

 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Based on the description, please try the following methods:

1.Create the new column to filter end of quarter.

IsEOQMonth = 
   IF(
       MONTH('Table'[Date]) = 3 || 
       MONTH('Table'[Date]) = 6 || 
       MONTH('Table'[Date]) = 9 || 
       MONTH('Table'[Date]) = 12, 
       1, 
       0
   )

2.Create the new measure for the target.

ConditionalGoal = 
   IF(
       MAX('Table'[IsEOQMonth]) = 1, 
       609, 
       203
   )

3.Try using the following dax formula.

Backlog Processed Goal % = 
   VAR PrevMonthCount = CALCULATE(DISTINCTCOUNT('risk_table'[risk_id]),PARALLELPERIOD('Calendar'[Date], -1, MONTH))
   VAR RiskCountLatest =
       IF(
           CALCULATE(DISTINCTCOUNT('risk_table'[risk_id])) = BLANK(), 
           0,
           CALCULATE(DISTINCTCOUNT('risk_table'[risk_id]), 'risk_table'[snapshot_dt] = MAX('risk_table'[snapshot_dt]))
       )
   VAR CurrentGoal = MAX('Calendar'[IsEOQMonth]) * 203
   RETURN
       DIVIDE(
           PrevMonthCount - RiskCountLatest,
           CurrentGoal
       )

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.