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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SriGaG1
Helper II
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 

SriGaG1_0-1663784620989.png

 

 

https://drive.google.com/file/d/12cItFy_wdfWHsbg0qLry93pFBd88LujY/view?usp=sharing

 

thanking you in advance

1 ACCEPTED SOLUTION
HoangHugo
Solution Specialist
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]))

View solution in original post

7 REPLIES 7
HoangHugo
Solution Specialist
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".

Capture 14.PNG

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

 

Thank you again

HoangHugo
Solution Specialist
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]))

@HoangHugo  Hello,

 

Thanks for the reply. No its not working.

 

SriGaG1_0-1663818135245.png

When I turned that one to a table,

 

SriGaG1_1-1663818181394.png

 

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

 

HoangHugo
Solution Specialist
Solution Specialist

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

Capture 13.PNG

 

See updated file, link below

https://drive.google.com/file/d/1eykhH1Md7m7jA-JeofZNVzjXkWt8JpRj/view?usp=sharing

Hello @HoangHugo 

 

Thankyou.

But this is what I get. 

Avg sales = CALCULATE( IFERROR(
    [LY Sales]/[Measurecal],
    BLANK()
    ),ALLSELECTED('Date'[Date]))
 
SriGaG1_0-1663819213818.png

Why this kind of things happennig?

@HoangHugo  Hope you didnt make any otherchange.. 😞

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors