Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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 )
)
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
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
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
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 )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
38 | |
26 | |
20 | |
15 | |
8 |
User | Count |
---|---|
69 | |
47 | |
46 | |
20 | |
15 |