Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 

 

Please HELP !

RunningTOT.jpg

10 REPLIES 10
eFeM135
Advocate II
Advocate II

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

@Bacu84,

You were glad to have found the forum, but did my feedback help in resolving your issue? Please provide feedback!

Matt_Alexander
Advocate III
Advocate III

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

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

 

 

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

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

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

 

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

v-chuncz-msft
Community Support
Community Support

@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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.