Cumulative sum of a measure per date

[madhushree]

Need help to calculate cumulative sum of a measure.

Below is the table what i need. Till X(MTD) i am able to do.

Date	Usage minutes	X(MTD)	Y
1-Oct	850	0.59	0.590278
2-Oct	1256	0.73	0.660764
3-Oct	900	0.70	0.672454
4-Oct	1200	0.73	0.686892
5-Oct	1440	0.78	0.706347

Description:

X(MTD) is a measure that give cumulated minutes used, calculated as below

for 1st oct : usage 850 and 1st day so X(MTD) is 850/(1*1440)=0.59

for 2nd oct : usage is 850+1256 and 2nd day so X(MTD) is 2106/(2*1440)=0.73

for 3rd oct : usage is 850+1256+900 and 3rd day so X(MTD) is 3006/(3*1440)=0.70

But for Y i need the sum X(MTD) measure and divided by day i.e.,

for 1st oct : X(MTD) 0.59 and 1st day so Y should be 0.59/1=0.59

for 2nd oct : X(MTD) 0.73 and 2nd day so Y should be 0.73/2=0.66

for 3rd oct : X(MTD) 0.70 and 3rd day so Y should be 0.70/3=0.67

I have already written formula for X(MTD),How can i write formula for y. As X(MTD) is measure i am not able to use sum([(MTD)]). 

[Ashish_Mathur]

Hi @madhushree,

Try this

=[X(MTD)]/DAY(MAX(CALENDAR[Date]))

In your visual, i have assumed that the Date field is coming from the Calrndar Table.  Also, there is a relationshion from the Date column of your base data table to the Date column of your Calendar table.

Hope this helps.

[v-sihou-msft · Accepted solution]

@madhushree

For your X(MTD), we can directly create a measure like below:

X = TOTALMTD(SUM(Usage[Usage minutes]),Usage[Date]) / (CALCULATE(SUM(Usage[Usage minutes]),LASTDATE(ALLSELECTED(Usage[Date]))) * TOTALMTD(COUNTROWS(),Usage[Date]) )

Then you want to calculate the rolling average based on above measure. However, this is a TOTALMTD() over another TOTALMTD(), to make calculation on measure value, we have to summarize it into a column. So I suggest you create a calculated table like: 

Table 2 = SUMMARIZE(Usage,Usage[Date],"x value",[X])

Build the relationship to source table on Date column. Then use TOTALMTD on that "x value" column.

Y = TOTALMTD(SUM('Table 2'[x value]),'Table 2'[Date])/TOTALMTD(COUNTROWS(),'Table 2'[Date])

Regards,