cancel
Showing results for
Did you mean:
Frequent 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
Community Support Team

## Re: Running Total - DAX

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.
Frequent Visitor

## Re: Running Total - DAX

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.

Member

## Re: Running Total - DAX

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

Frequent Visitor

## Re: Running Total - DAX

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

Member

## Re: Running Total - DAX

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

Frequent Visitor

## Re: Running Total - DAX

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

Frequent Visitor

## Re: Running Total - DAX

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]))))

Frequent Visitor

## Re: Running Total - DAX

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]

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(),

Frequent Visitor

## Re: Running Total - DAX

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]

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

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 6 members 1,113 guests
Recent signins: