Average Gross Amount (TODAY instead of full month)

[rphang]

I would need help for below I can't seems to get around the correct formula to use. Basically I am trying to achieve 2 results,

1. To show an average Gross Amount per day (excluding TODAY)

E.g if today is 17 Nov, I should have total Gross Amount (till 16th Nov) divided by 16 days (01 - 16 Nov).

120,346.50 / 16 = 7,521.66

Right now with my DAX, it is taking current Total Gross Amount per month divided by total days in the month. Therefore I am getting 4,011.55 instead of 7,521.66.

Avg MTD Sales = CALCULATE(SUM('Table1'[Gross_Amount__c]), DATESMTD('DateDIM'[Date])) / CALCULATE(COUNT('DateDIM'[Date]),DATESMTD('DateDIM'[Date]))

[v-robertq-msft · Accepted solution]

Hi, @rphang 

According to your description and sample data, I can roughly understand your requirement, you can try this method to achieve this:

Create two calculated columns like this:

Till Date Total Sales =

CALCULATE(SUM('Table'[Sales Amount]),FILTER(ALL('Table'),MONTH([Date])=MONTH(EARLIER('Table'[Date]))&&[Date]<=EARLIER('Table'[Date])))

Till Date Avg Daily Sales =

var _monthend=EOMONTH(MAX('Table'[Date]),0)

var _monthstart=DATE(YEAR(_monthend),MONTH(_monthend),1)

var _datediff=MAX('Table'[Date])-_monthstart

return

DIVIDE([Till Date Total Sales],_datediff)

Create a measure:

Flag =

IF(MAX('Table'[Date])<=TODAY(),1,0)

Then you can create a line chart and place the columns then apply a visual filter like this:

And you can get what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rphang]

@v-robertq-msft 

Exactly what I am looking for, thanks!!

[v-robertq-msft]

Hi, @rphang 

According to your description, I can clearly understand your requirement, I think you can try this measure:

This is the test data I created based on your description:

Avg MTD Sales =

var _monthend=EOMONTH(MAX('Table'[Date]),0)

var _monthstart=DATE(YEAR(_monthend),MONTH(_monthend),1)

var _datediff=MAX('Table'[Date])-_monthstart

var _sum=CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Date]))

return

DIVIDE(_sum,_datediff)

And you can create a table chart and place it like this to get what you want, like this:

You can download my test pbix file below

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rphang]

@v-robertq-msft 

Thank you for the formula.

Not sure if below table would help to elaborate. The formula will need to,

- calculate the month daily average (Total Sales Till Date divided by the days passed Till Date)

- identify "TODAY()" and stop the calculation from further calculation

- the results will need to be display on a line chart visual as well 🙂

Date	Sales Amount	Till Date Total Sales	Till Date Avg Daily Sales	Remark
1-Nov-21	876	876	876 / 1
2-Nov-21	234	1,110	1110 / 2
3-Nov-21	765	1,875	1875 / 3
4-Nov-21	234	2,109	2109 / 4
5-Nov-21	76	2,185	2185 / 5
6-Nov-21	23	2,208	2208 / 6
7-Nov-21	876	3,084	3084 / 7
8-Nov-21	4,363	7,447	7447 / 8
9-Nov-21	547	7,994	7994 / 9
10-Nov-21	7,696	15,690	15690 / 10
11-Nov-21	195	15,885	15885 / 11
12-Nov-21	1,295	17,180	17180 / 12
13-Nov-21	325	17,505	17505 / 13
14-Nov-21	2,357	19,862	19862 / 14
15-Nov-21	234	20,096	20096 / 15
16-Nov-21	547	20,643	20643 / 16
17-Nov-21	14	20,657	20657 / 17
18-Nov-21	6,823	27,480	27480 / 18
19-Nov-21	432	27,912	27912 / 19
20-Nov-21	4,363	32,275	32275 / 20
21-Nov-21	124	32,399	32399 / 21
22-Nov-21	457	32,856	32856 / 22
23-Nov-21	124	32,980	32980 / 23
24-Nov-21	4,512	37,492	37492 / 24
25-Nov-21		37,492	0	< Today
26-Nov-21		37,492	0
27-Nov-21		37,492	0
28-Nov-21		37,492	0

Thank you!

[Gabriel_Walkman]

Maybe something like this would work. That is, if you are using months as the x-axis.

Daily average =

calculate(

    divide( sum('Table1'[Gross_Amount__c]), distinctcount( 'Table1'[Date] ) ),

    'Table1'[Date] <> today()

)