Calculate Month to Date Average Production for Current Month

[bigmona89]

Hi All!

I have been banging my head against the wall trying to figure out the DAX expressions to caculate a month to date average. I am relatively new to Power BI and I am absolutely loving it, but there is a bit of a learning curve for me.

I have tried the AverageX function so many times but can't seem to make it work for what I need. Essentially what I am trying to do is create a month to date average for the current month. So, for example, if my dataset is a rolling 30 days (lets say 11/15/2021 - 12/15/2021) I would want to calculate the MTD average production per day for 12/1 - 12/15. So it would be Total Production for Current Month  / number of days passed in current month = MTD Daily Average

However, once another day passes and the dataset changes to 11/16/2021 - 12/16/2021 I wouldn't want the dax expression to be static. It should know to include the new day into the calculation (month to date average for 12/1 - 12/16). Could someone point me in the right direction as to what expression to use? I am so lost. Thanks a ton! I've attached an excel table with mock data. 

Date	Division	Production
11/15/2021	Haynesville	470,978
11/16/2021	Haynesville	207,166
11/17/2021	Haynesville	206,027
11/18/2021	Haynesville	110,255
11/19/2021	Haynesville	222,234
11/20/2021	Haynesville	873,405
11/21/2021	Haynesville	121,769
11/22/2021	Haynesville	651,864
11/23/2021	Haynesville	1,326,766
11/24/2021	Haynesville	102,000
11/25/2021	Haynesville	78,600
11/26/2021	Haynesville	508,940
11/27/2021	Haynesville	1,245,385
11/28/2021	Haynesville	1,381,440
11/29/2021	Haynesville	830,456
11/30/2021	Haynesville	1,140,000
12/1/2021	Haynesville	570,000
12/2/2021	Haynesville	1,140,000
12/3/2021	Haynesville	1,995,000
12/4/2021	Haynesville	285,000
12/5/2021	Haynesville	285,000
12/6/2021	Haynesville	1,140,000
12/7/2021	Haynesville	1,245,385
12/8/2021	Haynesville	1,381,440
12/9/2021	Haynesville	830,456
12/10/2021	Haynesville	855,000
12/11/2021	Haynesville	1,326,766
12/12/2021	Haynesville	102,000
12/13/2021	Haynesville	508,940
12/14/2021	Haynesville	1,245,385
12/15/2021	Haynesville	1,381,440

[CNENFRNL · Accepted solution]

No worries, pal. After a short, steep learning curve, demystified DAX is just "filter + aggregation", to my understanding.

Let's get back on track,

[VahidDM · Accepted solution]

Hi Mate,

You can also use this measure:

MTD Daily Average = 
CALCULATE( AVERAGE( 'Table'[Production] ), DATESMTD( 'Table'[Date] ) )

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[bigmona89]

Awesome! This worked great man, really appreciate the response. You and @CNENFRNL really helped me out a ton. Again, much appreciated!

[CNENFRNL · Accepted solution]

No worries, pal. After a short, steep learning curve, demystified DAX is just "filter + aggregation", to my understanding.

Let's get back on track,

[bigmona89]

Man you made that look incredibly easy! I had no idea that there was a DATESMTD formula, and I looked for it, googled it, etc. Really appreciate the help man, my report is working perfectly now.