Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

PowerBi Average

Hi all,

I’m new to PowerBI. Trying to display the average of previous day’s data (excluding today’s data) E.g. average 1st – 9th June. I’ve created the following formula so far, but it sums up till today’s (10th June) instead of only till 9th June data? How can I avoid including today’s data (10th June)?

 

 

Avg =

 

if ( EOMONTH(max('Date'[Date]);0)=EOMONTH(today();0);

(   TOTALMTD(SUM(Values[Quantity]);DATEADD(DATEADD('Date'[Date];0;Month);-1;Day))/day(today()-1)   );

 

(   TOTALMTD(SUM(Values[Quantity]);DATEADD(DATEADD('Date'[Date];0;Month);0;Day))/ day(EOMONTH(max('Date'[Date]);0))   )   )

 

Any ideas?

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Anonymous  Instead of using TOTALMTD(), another way you might want to try is to manually set a start and end dates. So the measure looks like this:

     

    calcQuantity =

    var thisMonthStart = DATE(YEAR(TODAY()),MONTH(TODAY()),1)
    var thisMonthEnd = TODAY() - 1
    return CALCULATE(AVG('Values'[Quantity]),
    ALL(dim_date),
    dim_date[calendar_date] >= thisMonthStart &&
    dim_date[calendar_date] <= thisMonthEnd
    )