Average Daily Spend by Month

[sprather]

New to PowerBI. I have the following data that I want to be able to see the average daily spending by month. So for the current month we are on day 19. I want to use the # of days in the month if it is a previous. For Current month I would want this factored to exclude the current day as the day is not complete and will skew the average.

I would like to be able to see historical so selecting April I would see th April Average Daily Spending as well....The data spans years so I cannot only look at the month - the year would be required.

I would also like to be able to group them by the class so in the example below I would like to be able to show the classes such as below individually or grouped together:

Carlsbad Yard

Carlsbad Inventory

Carlsbad Office

Sorry for so much but I'm just needing a place to start:

Any help will be greatly appreciated

Shane

[Greg_Deckler]

I would start with just the default Average aggregation for Total Price. For month to date, I would use TOTALMTD function.

https://msdn.microsoft.com/en-us/library/ee634560.aspx

[sprather]

Thanks! I will try that. There are so many functions I am mainly looking for a palce to start. I appreciate you giving me one. I will let you know if that is all I needed!

[v-huizhn-msft]

Hi @sprather,

You want to create the average for each month. You can use the formula to create a calculated column.

Average=CALCULATE(Average(Table[value]),ALLEXCEPT(Table,Table[Month]))

If this is not what you want, you'd better share your sample data or .pbix file for further analysis. And your requirement is still confusing, please describe it clearly.

Best Regards,
Angelia

[sprather]

I will play with this function you provided but let me give more precise details.

This is for our PO system in QuickBase and we have a target of $3200/day of spending for our yards. When one of the owners pulls up the PowerBI app they will want to see month-to-date average $ spent/day to see if we are on pace. So for example today is day 22 of May. This report should show the owners if they pull up the portal for spending what our average daily spend is for May. Ideally be able to pull up historical data as well so they can compare over time the average daily spending by month.

For Current Month the value should be the total $ spent through the day prior to the current day divided by the # of days in the month - today. So if we have spent 25000 through the 21st (we do not want to include the current day in the calculation as this days spending has not been completed).

For previous months the calculation should be total $ spent for the month divided by the number of days in the month.

I have already configured a interface to pull all the data from QuickBase using the QuNext ODBC driver/on-premise gateway with scheduled data pulls. So I have all the data flowing....Now I just have to understand the syntax requirements for manipulating the data effectively in PowerBI.

Hope this makes sense....

[v-huizhn-msft]

Hi @sprather,

Have you resolved your issue? If you have, please mark the right reply as answer, or welcome to share your solution, more people will find workaround easily.

Thanks,
Angelia

[v-huizhn-msft · Accepted solution]

Hi @sprather,

>>So for example today is day 22 of May. This report should show the owners if they pull up the portal for spending what our average daily spend is for May. So if we have spent 25000 through the 21st 

You can create a measure to get your result.

Average_Current=CALCULATE(SUM(Table[price]),FILTER(Table, Table[Month]=MAX(Table[Month])))/CALCULATE(COUNT(Table[purchase_date]),FILTER(Table, Table[Month]=MAX(Table[Month])&&FILTER(Table, Table[purchase_date]<MAX(Table[purchase_date])))

>>Ideally be able to pull up historical data as well so they can compare over time the average daily spending by month. 

Please try to create the following formula, please test it using your sample data.

average = Var currentmonth=MAX(Table[Month])
RETURN 
IF(
	FIRSTNONBLANK(MAX(Table[Month]),1)=currentmonth,
	Table[Average_Current],
	CALCULATE(AVERAGE(Table[price]),FILTER(Table,Table[Month]))
	
)

Best Regards,
Angelia