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

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