cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Running Total - DAX

Hi Guys

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.

RUNTOT = CALCULATE([TOTAL SALES], DATESBETWEEN(Query1[NewDate],MIN(Query1[NewDate]),MAX(Query1[NewDate])))

10 REPLIES 10
Highlighted
Community Support

You may take a look at the following articles.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

Thanks for your contribution,

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.

Thanks again for your time

Highlighted

Hi @Bacu84

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?

Regards

Highlighted
Regular Visitor

HI MAtt

Thanks a lot for your tip is still not working, please see the last column called CumSALES: I sued the YTD formula as follows:

TOTALYTD(SUM(Query1[NET SALES]),Query1[NewDate],YEAR(Query1[NewDate])=2017, "31/12/2017"), It`s just replicating the same NET SALES values

Highlighted

Hi @Bacu84

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.

Matt

Highlighted
Regular Visitor

Matt

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:

RunTOT = TOTALYTD(SUM(Query1[NET SALES]),Query1[CalendarDate],ALL(Query1))

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

Highlighted
Regular Visitor

Hi Matt

I found the solution by inserting an Index column But it`s keeping telling me that there is not enough memory for it ???????????

Seriously ????

The formula is the one behind:

RunTOT = IF(Query1[CalendarYear]= 2017,CALCULATE(SUMX(Query1,Query1[NET SALES]), FILTER(Query1,Query1[Index] > Earlier(Query1[Index]))),CALCULATE(SUMX(Query1,Query1[NET SALES]), FILTER(Query1,Query1[Index] > Earlier(Query1[Index]))))

Highlighted

As indicated in an earlier post, you need to create a date dimension (best to follow our master Marco Russo on SQLBI.com) https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/  and think twice before you accept the option in PowerBI to let PowerBI do this for you.

IMHO you're still thinking in SQL terms and not in DAX terms (and believe me I also had to step through this faze!):

- make sure you have a Date in Query1;

- create a date table (ie dimDate) with at last a Date field and the Year indicator, potentially with a dynamic query or taking the full blown version from our master:

- mark that dimDate table as a date table

- create a relationship between Query1.[Date] and dimDate.[Date]

Then review your measure:

YTD Total =
CALCULATE (

SUM ( Query1.[NET SALES] ),
DATESYTD ( dimDate.[Date] )
)

If your relationship is not active, then you'll need to add the USERELATIONSHIP ( Query1[your_date], dimDate[Date] ), statement the line after SUM(),

Highlighted

(my reply somewhat got lost)

As indicated in an earlier post, you need to create a date dimension (best to follow our master Marco Russo on SQLBI.com) https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/ and think twice before you accept the option in PowerBI to let PowerBI do this for you.

IMHO you're still thinking in SQL terms and not in DAX terms (and believe me I also had to step through this faze!):

- make sure you have a Date in Query1;

- create a date table (ie dimDate) with at last a Date field and the Year indicator, potentially with a dynamic query or taking the full blown version from our master:

- mark that dimDate table as a date table

- create a relationship between Query1.[Date] and dimDate.[Date]

Then review your measure:

YTD Total =
CALCULATE (

SUM ( Query1.[NET SALES] ),
DATESYTD ( dimDate.[Date] )
)

If your relationship is not active, then you'll need to add the USERELATIONSHIP ( Query1[your_date], dimDate[Date] ), statement the line after SUM(),

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors