cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Daily Average MTD

Daily Average MTD?

I seem to have a problem getting the proper calculation. I got a working current Daily Average MTD base on the date filter but prefer for it to auto calculate rather than me changing the filter. But my current one is having to rely on the filter.

Current Calculation -

`MTD Daily Average = AVERAGE('Date Filter', [Total Sales])`

Thoughts?

Kris
1 ACCEPTED SOLUTION

Yep, that's great. Assuming your calendar table follows the rules for in built time intelligence functions (read here  http://exceleratorbi.com.au/power-pivot-calendar-tables/) you can use the following formulas

Sales MTD =TOTALMTD(factsales[sales amount])

total sales days = distinctcount(factsales[date])

daily average = divide([sales mtd],[total sales days])

i think this will do what you want

* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
4 REPLIES 4

It is impossible to help you unless you provide information about how your tables are loaded and related, and where the data is that you are trying to average.

* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Resolver I

Hello Matt,

Fair enough. My tables are loaded or imported from SQL. I have two tables, one is Date Filter and the other is FactSales which includes a column for Posting Date, Document No., Customer No., and Sales Amount. I have

My current calculation works as it uses the Date filter and Sales Amount using the AVERAGEX. I was hoping to get a MTD Daily Average base on the Posting Date at its current month to get an average without me having to ensure to change the month and year filter.

Hopefully that's enough info? Thanks for the response.

Kris

Yep, that's great. Assuming your calendar table follows the rules for in built time intelligence functions (read here  http://exceleratorbi.com.au/power-pivot-calendar-tables/) you can use the following formulas

Sales MTD =TOTALMTD(factsales[sales amount])

total sales days = distinctcount(factsales[date])

daily average = divide([sales mtd],[total sales days])

i think this will do what you want

* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Resolver I

This is fantastic! I am using the built in time intelligence. Appreciate it!

I think where I was missing with the calculation was that I was trying to do it all in one line of code versus breaking it down to 3.

Kris