PowerBi Average

[Anonymous]

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?

[mochabits]

@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

)