Forum Discussion
Average Daily Spend by Month
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
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
6 Replies
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
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!
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
