Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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 |
Solved! Go to Solution.
@Anonymous
Try this calculated column:
TotalPerMonth = CALCULATE(SUM(Table1[Amount]),FILTER(Table1,Table1[Date] = EARLIER(Table1[Date])))
@Anonymous
Try this calculated column:
TotalPerMonth = CALCULATE(SUM(Table1[Amount]),FILTER(Table1,Table1[Date] = EARLIER(Table1[Date])))
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!
Hey @danb!
The code you posted creates an error "EARLIER/EARLIEST refers to an earlier row context which doesn't exist."
Any Ideas?
@Anonymous
Enter in the formula as a "New Column" and not a "New Measure"
Yup, I am entering it as a new column
@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.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |