locked
Running total dax RRS feed

  • Question

  • Hi,

    I have a calculation that gives me a running total

    CurrentBalance:= CALCULATE(SUM(Transactions[Amount_OLD]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date]<= MAX('Calendar'[Date])))

    it works perfect if I use only dates, but my table has other columns that I can use as filter, for example "Document" and when I add the slicer the current balance doesnt work properly. 

    thanks

    Astrid

    Friday, July 12, 2019 3:03 PM

Answers

  • Thanks, but that doesn't work either.

    I do get the current balance of the whole table by date, but it does not filter the customer. 

    Initially it was filtering by dates and customers. but now, it is not. :(


    Sorry, that would be a side effect of using ALL() to stop the document column from also filtering the results. We can fix that by bringing back the context from the Customer and Salesrep tables. I'm assuming in the code below that the Customer and SalesRep tables join to the transactions table based on their hash key columns (so the code below in bold is meant to represent the columns used in the relationships to Transactions)

    CurrentBalance:=
    CALCULATE(
        SUM(Transactions[
    Amount_OLD]),
        FILTER(ALL(
    Transactions),Transactions[SequenceNo]<=MAX(Transactions[SequenceNo]))
        VALUES(  Customer[CustomerHashKey] ),
        VALUES(  SalesRep[SalesRepHashKey] )
    )


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by astridaku Monday, July 22, 2019 2:03 PM
    Saturday, July 20, 2019 1:40 AM

All replies

  • Are you using a Data dimension table in the model? If yes, is it set as Data Table?

    Time Intelligence needs that setup to work properly.

    Saturday, July 13, 2019 5:24 PM
  • it works perfect if I use only dates, but my table has other columns that I can use as filter, for example "Document" and when I add the slicer the current balance doesnt work properly. 

    Can you explain what you mean by "doesnt work properly". It looks like you have a separate date dimension table so on the surface it looks like this calculation should work fine. This is assuming that [Amount_OLD] is a column, not a measure, if it is a measure we'd need to know what the expression is for it.


    http://darren.gosbell.com - please mark correct answers

    Sunday, July 14, 2019 11:27 PM
  • Hi Astrid,

    You description is not quite clear to us. What do you mean by the sentence "when I add the slicer the current balance doesnt work properly"?

    Could you make a clarification about this? Or provide some sample data or code to support your problem?

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 15, 2019 3:04 AM
  • Hi, do you mean dimension tables? yes I am using a few. i will add the model design
    Monday, July 15, 2019 2:31 PM
  • Hi,

    Here is the design of the model and I sample data. I tried to upload the excel but i am not sure how.

    I have a few dims and a fact table.

    My running total slice by date, by not by documents.

    can it be that it slice by date, because the table is connected to a dimcalendar table, but the documents is not? do i to create a document dim?

    Monday, July 15, 2019 2:36 PM
  • can it be that it slice by date, because the table is connected to a dimcalendar table, but the documents is not? do i to create a document dim?

    Probably not, but we don't really have enough information to answer this question. The information in your last post is helpful, but we still have not idea what you mean by "doesnt work properly".

    What results are you actually seeing and what results are you expecting to see? 

    Is [Amount_OLD] a measure or a column? If it's a measure can you post the expression for it? 


    http://darren.gosbell.com - please mark correct answers

    Monday, July 15, 2019 9:01 PM
  • Amount_OLD is a column that we bring from the database, it is not a measure.

    Here is what I need

    My CurrentBalance column is 

    CurrentBalance:= CALCULATE(SUM(Transactions[Amount_OLD]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date]<= MAX('Calendar'[Date])))

    when I add dates, I get good results, even though it shows every day and not only when I have transactions.

    But when I add the transaction type (document), I get weird results.

    The invoice column is the Amount_OLD calculated as a measure:

    Invoice:= CALCULATE(sum(Transactions[Amount_OLD]))

    Thanks

    Astrid

    Thursday, July 18, 2019 2:36 PM
  • Your running total expression is only looking at dates, so when you add document if filters so that it does a running total just for the current document (or any prior records with the same value in the Document column)

    If you want the running total to work across more than just dates you'd probably need something like a transaction sequence number or something like that so we have a single column that we can use to figure out which transaction rows are earlier than the current row.

    Then your running sum would become something like the following:

    CurrentBalance:= CALCULATE(SUM(Transactions[Amount_OLD]),FILTER(ALL('Transactions'[SequenceNo]),'Transactions'[SequenceNo]<= MAX('Transactions'[SequenceNo])))


    http://darren.gosbell.com - please mark correct answers

    Friday, July 19, 2019 12:12 AM
  • Thanks, but it doesn't work.

    I created a sequence number column on the table, simple 

    row_number() over (order by TRA.TRXDate) AS SequenceNo

    and then i did the dax 

    CurrentBalance:= CALCULATE(SUM(Transactions[Amount_OLD]),FILTER(ALL(Transactions[SequenceNo]),Transactions[SequenceNo]<=MAX(Transactions[SequenceNo])))

    and now, now matter what filter I use I get

    for example by dates. i don't even have a running total yet.

    thanks

    astrid

    Friday, July 19, 2019 1:20 PM
  • Sorry, I should have put a table reference not the column reference inside the ALL function

    CurrentBalance:= CALCULATE(SUM(Transactions[Amount_OLD]),FILTER(ALL(Transactions),Transactions[SequenceNo]<=MAX(Transactions[SequenceNo])))


    http://darren.gosbell.com - please mark correct answers

    Friday, July 19, 2019 2:17 PM
  • Thanks, but that doesn't work either.

    I do get the current balance of the whole table by date, but it does not filter the customer. 

    Initially it was filtering by dates and customers. but now, it is not. :(

    Friday, July 19, 2019 2:28 PM
  • Thanks, but that doesn't work either.

    I do get the current balance of the whole table by date, but it does not filter the customer. 

    Initially it was filtering by dates and customers. but now, it is not. :(


    Sorry, that would be a side effect of using ALL() to stop the document column from also filtering the results. We can fix that by bringing back the context from the Customer and Salesrep tables. I'm assuming in the code below that the Customer and SalesRep tables join to the transactions table based on their hash key columns (so the code below in bold is meant to represent the columns used in the relationships to Transactions)

    CurrentBalance:=
    CALCULATE(
        SUM(Transactions[
    Amount_OLD]),
        FILTER(ALL(
    Transactions),Transactions[SequenceNo]<=MAX(Transactions[SequenceNo]))
        VALUES(  Customer[CustomerHashKey] ),
        VALUES(  SalesRep[SalesRepHashKey] )
    )


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by astridaku Monday, July 22, 2019 2:03 PM
    Saturday, July 20, 2019 1:40 AM
  • Thanks, it worked, but it takes forever to process and the table only has like 100,000 records.

    but most important it works, I will look into performance issues now

    thanks again

    Monday, July 22, 2019 2:05 PM