Forum Discussion

Bacu84's avatar
Bacu84
Regular Visitor
8 years ago

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.201...
Bacu84's avatar
Bacu84
Regular Visitor
8 years ago

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

 

 

 

 

 

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    Bacu84
    Regular Visitor
    8 years ago

    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

     

    • Bacu84's avatar
      Bacu84
      Regular Visitor
      8 years ago

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

       

       

       

      • eFeM135's avatar
        eFeM135
        Advocate II
        8 years ago

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