Forum Discussion

vejas's avatar
vejas
Helper I
8 years ago
Solved

running total DAX

I have related tables - Debt_table ,Calendar

 

Debt table columns:

 

Date | Client | Sell/Payment

 

I calculate running total , to know clients debt balance to date.

 

Total Debt = CALCULATE([Total Sell/Payment],FILTER(ALL(Debt_table),Debt_table[Date]<=MAX(Debt_table[Date])))

 

I make Pivot table 

 

Date | Total Debt  -  it show good numbers. When i add Client filter context i get nonsense. How to force respect Client filter ? 

 

 Data sample:

 

ClientDateSell/Payment
00012005-04-04-141.19
02122005-04-06310.68
02122005-04-06503.48
00012005-04-06138.58
02122005-04-08-1.39
02122005-04-081.39
02122005-04-08-7.82
02122005-04-087.82
00012005-04-1273.42
00012005-04-13-175.08
02122005-04-14444.16
02122005-04-14149.96
02122005-04-19-109.79
02122005-04-19-20.77
02122005-04-19730.68
00012005-04-19141.19
02122005-04-20124.4
00012005-04-25135.54
02122005-04-26655.93
02122005-04-26786.39
02122005-05-03978.67
02122005-05-05482.5
02122005-05-06155.89
00012005-05-09-138.58
00012005-05-09-248.49

 

 

Without Client Running total works fine:

 

YYYY-MMTotal Sell/PaymentRunning Total
2005-0437483748
2005-0539457693
2005-0623009993
2005-07291412907
Grand Total1290712907

 

 

Adding a Client and something goes wrong.

 

YYYY-MMClientTotal Sell/PaymentRunning Total
2005-04021235753748
2005-05021242197518
2005-06021221059993
2005-070212318812907
Grand Total 1308812907
Need Help
  • vejas's avatar
    vejas
    8 years ago

    Solution is to run FILTER against Calendar table , not Debt_table.

     

    CALCULATE([Total Sell/Payment],FILTER(ALL('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])))

9 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    8 years ago

    Hi vejas

     

    Possibly you might need to change your filter context to include a clause for your Client.

     

    And does your Debt_Table have consecutive dates for all dates, even where there is no data?

     

    Do you have some sample data?

  • BILASolution's avatar
    BILASolution
    Solution Specialist
    8 years ago

    Hi vejas

     

    Try this...

     

    1. The next picture shows the sample data... (Table1)

     

     

    2. I created a calculated column with the next DAX...

     

     

    Accumulative Payment = var cli = Table1[Client] var dat = Table1[Date] return CALCULATE(SUM(Table1[Payment]);CALCULATETABLE(ALL(Table1);Table1[Client] = cli);Table1[Date] <= dat ; Table1[Client] = cli)

    3. I created the next measures with DAX...

     

     

     

    Total Payment = SUM(Table1[Payment]) 
    Total Accumulative Payment = SUM(Table1[Accumulative Payment])

    4. The final report looks like this...( after hiding useless columns)

     

     

     

     

    Regards

    BILASolution

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi vejas,

     

    Try this

     

    =CALCULATE([Total Sell/Payment],FILTER(ALL(Debt_table[Date]),Debt_table[Date]<=MAX(Debt_table[Date])))

     

    Hope this helps.

    • vejas's avatar
      vejas
      Helper I
      8 years ago

      This is not working. (Debt_table[Date]) dont work even without Client.

       

      Thanks