Forum Discussion

jorge_oliveira's avatar
jorge_oliveira
Icon for Helper I rankHelper I
4 years ago
Solved

YTD average time - reset each year

Hi,

 

I have a line chart with a daily average response time and a YTD average response time, but I need to reset this YTD average time when the year changes. Is this possible? 

The measure that I'm using is this:

 

YTD_Average Time = CALCULATE(AVERAGE(COMPLAINTS[RESPONSE_TIME]); COMPLAINTS[RESPONSE_TIME]<>BLANK(); FILTER(VALUES(COMPLAINTS[RESPONSE_TIME]); COMPLAINTS[RESPONSE_TIME] < MAX('Calendar'[Date]));YEAR(COMPLAINTS[RESPONSE_TIME])<=year(today()))

 

 The chart is as following:

 

The blue line is the YTD average response time.

 

What do I need to change in the measure for it reset for 2022?

 

Thanks for your help.

Jorge

  • johnt75's avatar
    johnt75
    4 years ago

    You are comparing the year of the response time to today, not to the date from your calendar table. Try

    YTD_Average Time =
var maxDate = MAX ( 'Calendar'[Date] )
CALCULATE (
    AVERAGE ( COMPLAINTS[RESPONSE_TIME] );
    COMPLAINTS[RESPONSE_TIME] <> BLANK ();
    FILTER (
        VALUES ( COMPLAINTS[RESPONSE_TIME] );
        COMPLAINTS[RESPONSE_TIME] < maxDate
    );
    YEAR ( COMPLAINTS[RESPONSE_TIME] ) = YEAR ( maxDate )
)
  • v-cazheng-msft's avatar
    v-cazheng-msft
    4 years ago

    Hi jorge_oliveira ,

     

    You need another Measure to help you with that.

     

    YTD_Reset_Year =
CALCULATE (
    [YTD_Average Time],
    FILTER (
        'Calendar',
        YEAR ( 'Calendar'[Date] ) = SELECTEDVALUE ( 'Calendar'[Year] ) 
        //If you just need the YTD of 2022, please replace SELECTEDVALUE part with 2022
    )
)

     

     

    Then the result will look like this.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

     

    Best Regards,

    Community Support Team _ Caiyun

3 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    You are comparing the year of the response time to today, not to the date from your calendar table. Try

    YTD_Average Time =
var maxDate = MAX ( 'Calendar'[Date] )
CALCULATE (
    AVERAGE ( COMPLAINTS[RESPONSE_TIME] );
    COMPLAINTS[RESPONSE_TIME] <> BLANK ();
    FILTER (
        VALUES ( COMPLAINTS[RESPONSE_TIME] );
        COMPLAINTS[RESPONSE_TIME] < maxDate
    );
    YEAR ( COMPLAINTS[RESPONSE_TIME] ) = YEAR ( maxDate )
)
  • v-cazheng-msft's avatar
    v-cazheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi jorge_oliveira ,

     

    You need another Measure to help you with that.

     

    YTD_Reset_Year =
CALCULATE (
    [YTD_Average Time],
    FILTER (
        'Calendar',
        YEAR ( 'Calendar'[Date] ) = SELECTEDVALUE ( 'Calendar'[Year] ) 
        //If you just need the YTD of 2022, please replace SELECTEDVALUE part with 2022
    )
)

     

     

    Then the result will look like this.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

     

    Best Regards,

    Community Support Team _ Caiyun

  • jorge_oliveira's avatar
    jorge_oliveira
    Icon for Helper I rankHelper I
    4 years ago

    Thanks to all the users who heldped me. I'm sorry for the delay, but I've been away for some time. Back now.

    Thanks,

    Jorge Oliveira