cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bacu84 Frequent Visitor
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])))

 

 

Please HELP !

RunningTOT.jpg

10 REPLIES 10
Community Support Team
Community Support Team

Re: Running Total - DAX

@Bacu84,

 

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

Re: Running Total - DAX

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

                                                            RunningTOT2.jpg

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

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

 

 

 

 

CumSALES1.jpg

 

 

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

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

 

RunningTotalSecond.jpg

Bacu84 Frequent Visitor
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]))))

 

 

NOtmemory.jpg

 

eFeM135 Frequent Visitor
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]

 

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

eFeM135 Frequent Visitor
Frequent Visitor

Re: Running Total - DAX

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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

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

Top Ideas
Users Online
Currently online: 6 members 1,113 guests
Please welcome our newest community members: