Glad I found this forum because It`s two days I`m stuck on the runinng total calculation, I tried almost everything, basically I have a YOY comparison from the 02.01.2017 to the 21.03.2017 and from the 01.01.2018 to the 20.03.2018, I need to write a formula which can give me in a separate column the running total for the NET Sales column, I tried the formula below in DAX but is not working, just to clarify I had to create a new measure TOTAL SALES which is equal to SUM(Query1.[NET SALES]) and use this in the formula.
Unfortunately I`m still stuck with the issue, I try to better describe the complexity of the issue here, I have two comparable sets of date adjusted dates belonging to 2017 and to 2018, the main issue is to obtain a calculated column which can give me the running total in 2017 and then start back from zero and calculate the running total in 2018, I presume a conditional statement which filters out the relevant dates from the sum would be necessary. in the screenshot an example where the dates are passing from 2017 into 2018 and it`s there that the running total needs to stop and start back from zero.
You may be able to achieve this with TOTALYTD (TOTALYTD) and SAMEPERIODLASTYEAR (SAMEPERIODLASTYEAR), although perhaps that would only compare 01/01/2018-21/03/2018 against 01/01/2017-21/03/2017, I noticed you mention 02/01/2017?
OK, have you tried as a measure instead of a calculated column?
Then if you use the measure in a visual like a vertical bar chart with a date field on the x-axis, I believe it should aggregate the cumulative value for the year, each month. Might be a problem though if you have future dates in the date calendar as future months would calculate as the cumulative up to the current month.
I actually got the first step, it`s calculating but is not doing by row, is doing by date because that`s what I specified in the formula, there might be a workaround on this ? Please have a look at the formula and the results that are generating:
I`m quite sure that changing the last filter ALL in something else could trigger the row by row sum that I`m expecting, It`s even calculating the 2016 and 2017 separetely which is great but Im` missing the last bit which is th row by row sum