cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adnanzakir
Helper III
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: 

 

adnanzakir_0-1618099322322.png

 

1 ACCEPTED SOLUTION

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

v-angzheng-msft_0-1618391936565.png

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

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

v-angzheng-msft_1-1618391966693.png

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.

View solution in original post

6 REPLIES 6
v-angzheng-msft
Community Support
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

v-angzheng-msft_0-1618370000457.pngv-angzheng-msft_1-1618370025551.png

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

v-angzheng-msft_2-1618370205767.png

v-angzheng-msft_3-1618370255098.png

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.

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

 

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

v-angzheng-msft_0-1618391936565.png

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

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

v-angzheng-msft_1-1618391966693.png

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.

View solution in original post

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
Community Support
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:

v-angzheng-msft_0-1618276962299.png

Result:

v-angzheng-msft_1-1618276962302.png

 

 

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.

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. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors