Forum Discussion

SriGaG1's avatar
SriGaG1
Icon for Helper II rankHelper II
4 years ago
Solved

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 

 

 

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

 

thanking you in advance

  • 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

  • HoangHugo's avatar
    HoangHugo
    Icon for Solution Specialist rankSolution 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's avatar
    HoangHugo
    Icon for Solution Specialist rankSolution 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".

    • SriGaG1's avatar
      SriGaG1
      Icon for Helper II rankHelper II

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

       

      Thank you again