Conditional Formatting based on quarter elapsed

[Anonymous]

Hi Guys. 

I am trying to work out conditional fomatting for the following scenario. I have targets & actuals table, and also a date table. I have two measures, sum of targets and sum of actuals for each state. Now suppose if 50% of the quarter has elapsed, the state that has achieved 100% of targets based on 50% Quarter Elapsed to show green, achieved 90% of targets based on 50% Quarter Elapsed to show yellow, and anything below that as red. In the example below, as of 50% of quarter elapsed, QLD is green, NSW achieved 90% of quarter elapsed as yellow and VIC as red: 

[v-angzheng-msft]

Hi, @Anonymous 

Thank you for your feedback, I'm sorry I didn't give you an accurate answer
please don't worry about that, We can choose fields that need to apply conditional format.
In this case, let's right-click the % achieved  column and select the conditional formatting

Then select the background color and you could get the desired result based on the flag color field

Please check my attachment sample

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-angzheng-msft this is great. Exactly to what I was looking for. Now just one last mystery to be solved, how would you calculate the rolling % of QTD passed. I've noticed that you have used a hard coded value: % of quarter = 0.50

 Is there a way to have this value change dynamically on daily basis? based on the number of days passed in the quarter

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 
Thank you for your feedback.

First, to create a calculate table which records the date for the current full year:

Table 2 = CALENDAR(DATE(2021,1,1),DATE(2021,12,31))

Create a calculated column to represent the quarter of the current date：

Current_quarter = QUARTER('Table 2'[Date])

You also need a measure to calculate the percentage of the current quarter that has passed

_%of quarter(dynamically) = 
var days_of_quarter=CALCULATE(COUNTROWS('Table 2'),FILTER('Table 2',[Current_quarter]=QUARTER(TODAY())))
var Today_elapsed=CALCULATE(COUNTROWS('Table 2'),FILTER('Table 2',[Current_quarter]=QUARTER(TODAY())&&[Date]<=TODAY()))
return DIVIDE(Today_elapsed,days_of_quarter)

 I‘d like to note you that please replace % of the quarter with _%of quarter(dynamically),
Then you can get different results dynamically according to the current date

Please check my attachment sample, that's all I can do to help

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-angzheng-msft 

Thank you so much for your help. Your tricks helped out solve this problem of mine. Thank you so much buddy. 

[v-angzheng-msft]

Hi, @Anonymous 

Try to create measures below:

% of the quarter = 0.50

%Achieved total =
VAR StageGoal =
    SUM ( 'Table'[Target] ) * 'Table'[% of the quarter]
RETURN
    CALCULATE ( DIVIDE ( SUM ( 'Table'[Actual] ), StageGoal ) )

Flag color =
SWITCH (
    TRUE (),
    'Table'[%Achieved total] >= 1.00, "Green",
    'Table'[%Achieved total] >= 0.90, "Yellow",
    "Red"
)

Sample:

Result:

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-angzheng-msft 

thanks for the reply. I believe we are getting close with this. This is exactly what I was after, however, I would like the colours in the same column as %achieved, instead of a separate column of Flag Color.