Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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: 

 

 

Need Help
Show and Tell
Tips and Tricks
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    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.

6 Replies

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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. 

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        v-angzheng-msft
        Community Support
        5 years ago

        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.