topic Re: YTD average time - reset each year in DAX Commands and Tips

YTD average time - reset each year

jorge_oliveira — Tue, 29 Mar 2022 09:43:44 GMT

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

Re: YTD average time - reset each year

johnt75 — Tue, 29 Mar 2022 10:26:34 GMT

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

Re: YTD average time - reset each year

v-cazheng-msft — Mon, 04 Apr 2022 03:05:52 GMT

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

Re: YTD average time - reset each year

jorge_oliveira — Fri, 16 Sep 2022 11:39:36 GMT

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