cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper II

## Average Constant line creation is not working

I have created a line graph which is filtered using a date range picker. . And the chart is getting dynamially changed when we are back dating the values. Aditionally I needed an average value for Last year for the below chart. That means the dark blue color line.  As you can see, the average is a constant line.

``````Avg sales = CALCULATE( IFERROR(
[LY Sales]/[Measurecal],
BLANK()
), ALL('Date'[Date]))

LY Sales = CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date]))

Measurecal =
VAR StartDate =  DATE ( YEAR(MIN('Date'[Date])),MONTH(MIN('Date'[Date])), DAY(MIN('Date'[Date])) )
VAR EndDate =    DATE ( YEAR(MAX('Date'[Date])),MONTH(MAX('Date'[Date])), DAY(MAX('Date'[Date])) )
return DATEDIFF(StartDate,EndDate,DAY)``````

Below Is the chart that gives incorrect figure.
.  when we select the date period Jan 7 2021 to Nov 18 2021, the avrage should be 1172 not 989.13. And the average value constant line should  changed dynamically when we change the date range.  Can you pls help? I have attached the workbook

1 ACCEPTED SOLUTION
Solution Specialist

Hi

I think it is due to use ALL function, that will clear all Date filter in your whole page. So, try this one

Avg sales = CALCULATE( IFERROR(
[LY Sales]/[Measurecal],
BLANK()
), ALLSELECTED('Date'[Date]))

7 REPLIES 7
Solution Specialist

Hi, this maybe for your Date format, make sure Date column in your two table is same format.

In the line visual, you choose "Continous".

Helper II

Thankyou very much. I think its now working. Means alot. and you saved the day.. Lets close this off.

Thank you again

Solution Specialist

Hi

I think it is due to use ALL function, that will clear all Date filter in your whole page. So, try this one

Avg sales = CALCULATE( IFERROR(
[LY Sales]/[Measurecal],
BLANK()
), ALLSELECTED('Date'[Date]))

Helper II

@HoangHugo  Hello,

Thanks for the reply. No its not working.

When I turned that one to a table,

What could be the issue for this? The total value need to be populated in every row.

Solution Specialist

Did you follow me? only need change ALL to ALSELECTED.

Helper II

Hello @HoangHugo

Thankyou.

But this is what I get.

Avg sales = CALCULATE( IFERROR(
[LY Sales]/[Measurecal],
BLANK()
),ALLSELECTED('Date'[Date]))

Why this kind of things happennig?

Helper II

@HoangHugo  Hope you didnt make any otherchange.. 😞

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors