Calculated Column of Sum per Month

[Anonymous]

Hello!

I have a calculation to complete that requires a sum of the current month. Note, I need this to be done with a calculated DAX column, not a measure unfortunately! My calculation is pretty advanced and will be easier if i had this column.

To explain more clearly what I am looking for my desired output is shown below as TotalPerMonth:

Sales
Date	Amount	TotalPerMonth
7/1/2018	5	34
7/1/2018	6	34
7/1/2018	7	34
7/2/2018	2	34
7/2/2018	3	34
7/2/2018	3	34
7/3/2018	4	34
7/3/2018	3	34
7/3/2018	1	34
8/1/2018	2	39
8/1/2018	3	39
8/1/2018	3	39
8/2/2018	2	39
8/2/2018	3	39
8/2/2018	5	39
8/3/2018	6	39
8/3/2018	7	39
8/3/2018	8	39

The above table is should portray most of my caveats of my data, however, attached in the spoiler tag is a more rigororus dataset:

Spoiler

Date	Amount	TotalPerMonth
5/31/2018 0:00	53869.97	1259637
5/31/2018 1:00	54058.04	1259637
5/31/2018 2:00	53270.37	1259637
5/31/2018 3:00	54487.84	1259637
5/31/2018 4:00	54003.49	1259637
5/31/2018 5:00	52144.59	1259637
5/31/2018 6:00	47906.15	1259637
5/31/2018 7:00	49621.78	1259637
5/31/2018 8:00	54271.14	1259637
5/31/2018 9:00	53384.48	1259637
5/31/2018 10:00	50849.78	1259637
5/31/2018 11:00	53899.41	1259637
5/31/2018 12:00	54124.26	1259637
5/31/2018 13:00	53406.91	1259637
5/31/2018 14:00	48970.37	1259637
5/31/2018 15:00	48303.02	1259637
5/31/2018 16:00	52115.2	1259637
5/31/2018 17:00	53370.16	1259637
5/31/2018 18:00	51550.34	1259637
5/31/2018 19:00	51057.45	1259637
5/31/2018 20:00	52635.51	1259637
5/31/2018 21:00	53951.19	1259637
5/31/2018 22:00	54479.15	1259637
5/31/2018 23:00	53906.86	1259637
6/1/2018 0:00	52760.43	1234373
6/1/2018 1:00	52897.1	1234373
6/1/2018 2:00	50640.63	1234373
6/1/2018 3:00	52362.22	1234373
6/1/2018 4:00	51984.25	1234373
6/1/2018 5:00	51791.6	1234373
6/1/2018 6:00	48052.68	1234373
6/1/2018 7:00	49226.49	1234373
6/1/2018 8:00	53700.4	1234373
6/1/2018 9:00	53024.82	1234373
6/1/2018 10:00	52481.09	1234373
6/1/2018 11:00	53542.61	1234373
6/1/2018 12:00	52460.78	1234373
6/1/2018 13:00	52598.76	1234373
6/1/2018 14:00	52573.91	1234373
6/1/2018 15:00	52284.61	1234373
6/1/2018 16:00	52712.14	1234373
6/1/2018 17:00	47793.63	1234373
6/1/2018 18:00	46917.88	1234373
6/1/2018 19:00	48769.64	1234373
6/1/2018 20:00	51219.08	1234373
6/1/2018 21:00	53080.73	1234373
6/1/2018 22:00	50553.36	1234373
6/1/2018 23:00	50944.62	1234373

[danb · Accepted solution]

@Anonymous

Try this calculated column:

TotalPerMonth = CALCULATE(SUM(Table1[Amount]),FILTER(Table1,Table1[Date] = EARLIER(Table1[Date])))

[tharris]

Hello @danb ,

I am trying to accomplish the same thing, however, I do not have an import file, and therefore, cannot create that in power query...or use the EARLIER function as it is not supported for DirectQuery.

Do you have any ideas of how to achieve this?

Thanks!

[Anonymous]

Hey @danb!

The code you posted creates an error "EARLIER/EARLIEST refers to an earlier row context which doesn't exist."

Any Ideas?

[danb]

@Anonymous

Enter in the formula as a "New Column" and not a "New Measure"

[Anonymous]

Yup, I am entering it as a new column

[danb]

@Anonymous - it appears that you have some type of error occuring after the "earlier" function. Please see my screenshot below. I have the same column in with the May/June data and it is not tripping the error. 

[Anonymous]

Yes today is a monday, I was adding it as a measure. 

Your function works, thank you

I had to adjust it a bit however: 

I made a new column format(table1[date] , "yyyymm")

then filtered using that new column.

By filtering by date, it gives the sum/ per day instead of per month. big thanks though!