Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes! Register now.

Reply
CL7777
Helper III
Helper III

filtering average by only year in a chart where x axis is both year and month

I have a plot as follows:

CL7777_0-1667492530369.png

In this plot, I have a measure for the Avg 2021 and Avg 2022 lines shown

:

average usage time 2021 =
    VAR lastyear = YEAR(TODAY()) - 1
RETURN
CALCULATE([% Usage Time], ALLEXCEPT('_Date rt (2)', '_Date rt (2)'[Date Month]),'_Date rt (2)'[Year] = lastyear)
 
and
 
average usage time 2022 =
    VAR thisyear = YEAR(TODAY())
RETURN
CALCULATE([% Usage Time], ALLEXCEPT('_Date rt (2)', '_Date rt (2)'[Date Month]),'_Date rt (2)'[Year] = thisyear)
 
This produces 2 lines on the plot that represent the averages for each of the last 2 years. 
 
The problem is that the averages for each year extend into the other years. what I want is something that looks like the blue line in the plot below
 
CL7777_1-1667492962836.png

 

I want one line that shows the average value of the year being plotted. I have tried multiple measures but none of them produce this result.

 

Any help anyone can offer would be greatly appreciated.

 
 

 

 

3 REPLIES 3
Bifinity_75
Solution Sage
Solution Sage

Hi @CL7777 , can you send me a screenshot with the relationships of the dates, and what date are you using for the chart?. Thanks

Bifinity_75
Solution Sage
Solution Sage

Hi @CL7777 , try this measure

Average = CALCULATE(AVERAGE('Table'[Import]),ALLEXCEPT('Table','Table'[Date].[Year]))

The result:

Bifinity_75_0-1667506682018.png

Best regards

Thanks for your reponse @Bifinity_75. The plot that shows the bar chart is actually a measure and when I use the same idea as you had above but put averagex([measure]) instead, O get an incorrect result. the measure is:

 

% Usage Time =
DIVIDE(
       SUMx(
           'JobScan', MIN('JobScan'[LaborHours], 8)) ,
            SUMx(
               '_Date rt (2)', '_Date rt (2)'[Is Working Day]
             )
      )
 
I use the expression
CALCULATE(AVERAGEX('JobScan', [% Usage Time]), ALLEXCEPT('_Date rt (2)', '_Date rt (2)'[Date].[Year]))
since it is a measure, not a table column 
 
and I get instead the following blue line, which is not the desired result)
CL7777_0-1667576719875.png

 

any ideas why this might be wrong? thank you so much

 

 

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors
Top Kudoed Authors