cancel
Showing results for
Search instead for
Did you mean:
Helper III

## Conditional Formatting based on quarter elapsed

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:

1 ACCEPTED SOLUTION
Community Support

Hi, @adnanzakir
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.

6 REPLIES 6
Community Support

Hi, @adnanzakir

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.

Helper III

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

Community Support

Hi, @adnanzakir
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.

Helper III

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

Community Support

Hi, @adnanzakir

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.

Helper III

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.

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Check it out!

Click here to read more about the July 2021 Updates

#### Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors