DAX measure using variables is displaying column total as 0

[dimitrishuk]

Hi I'm facing a strange issue with a measure that is not SUMMING totals on the bottom powerpivot row or in power bi and cant figure out why. Is this a bug or some predictable behaviour that I'm missing?

The result should be 191,756.00 not 0 for the 1000+ days column. The strange thing is that a similar formula for 1-30 days works fine!!

Find a link to the sample pbix here

Formulas written out:

1000+ Days:=

     VAR
          MaxDate = MAX('Allocation Date Dimension'[Date])

     VAR BalanceBucketDebits =
          CALCULATE(
          SUM('Accounts Receivable Ledger'[Amount - Gross]),
          'Accounts Receivable Ledger'[Amount - Gross]>0,
          'Accounts Receivable Ledger'[Invoice Due Date]<=(MaxDate-1000),
          'Accounts Receivable Ledger'[Allocation Date]<=MaxDate
     )

     VAR BalanceBucketCredits =
     CALCULATE(
          SUM('Accounts Receivable Ledger'[Amount - Gross]),
          'Accounts Receivable Ledger'[Amount - Gross]<0,
          'Accounts Receivable Ledger'[Draft- Due Date]<=(MaxDate-1),
          'Accounts Receivable Ledger'[Allocation Date]<=MaxDate
     )

     VAR BucketBalance=

          BalanceBucketDebits+BalanceBucketCredits
     RETURN

     ROUND(IF(BucketBalance<0,0,BucketBalance),2)

Strange thing is, if I add row hierarchies sums work fine, and are correct but the total of all rows displays 0. The result should not be 0. Strangely, the following works fine!

1-30 Days:=

VAR MaxDate =

     MAX('Allocation Date Dimension'[Date])

VAR CurrentBucketDebits =

     CALCULATE(

          SUM('Accounts Receivable Ledger'[Amount - Gross]),

          'Accounts Receivable Ledger'[Amount - Gross]>0,

          'Accounts Receivable Ledger'[Invoice Due Date]>=(MaxDate-30),

          'Accounts Receivable Ledger'[Invoice Due Date]<=(MaxDate-1),

          'Accounts Receivable Ledger'[Allocation Date]<=MaxDate )

VAR LastBucketDebitsBalance =

     CALCULATE(

          SUM('Accounts Receivable Ledger'[Amount - Gross]),

          'Accounts Receivable Ledger'[Amount - Gross]>0,

          'AccountsReceivable Ledger'[Invoice Due Date]<=(MaxDate-31),

          'Accounts Receivable Ledger'[Allocation Date]<=MaxDate )

VAR Credits =

     CALCULATE(

          SUM('Accounts Receivable Ledger'[Amount - Gross]),

          'Accounts Receivable Ledger'[Amount - Gross]<0,

          'Accounts Receivable Ledger'[Draft- Due Date]<=(MaxDate-1),

          'Accounts Receivable Ledger'[Allocation Date]<=MaxDate )

VAR CreditBalanceBF=

     IF(LastBucketDebitsBalance+Credits<0,LastBucketDebitsBalance+Credits,0)

RETURN

ROUND(IF( CurrentBucketDebits+CreditBalanceBF<0, 0, CurrentBucketDebits+CreditBalanceBF ),2)

Whats the difference??

[dimitrishuk]

Strange, here is the dropbox link!

[v-qiuyu-msft]

Hi @dimitrishuk,

After looking into your pbix file, I find the BalanceBucketDebits + BalanceBucketCredits value is less than 0, so based on the condition IF( CurrentBucketDebits+CreditBalanceBF<0, 0, CurrentBucketDebits+CreditBalanceBF ), the final value for the measure 1000+ Days returns 0. So what you got is expected. 

Best Regards,
Qiuyun Yu 

[dimitrishuk]

Ok, so perhaps I need to change my measure a little. This is what I want to achieve.

Each row for address number returns a result.

0 if CurrentBucketDebits+CreditBalanceBF < 0, else returns the sum of CurrentBucketDebits+CreditBalanceBF. As you correctly point out that condition correctly returns 0.

The outcome I am looking for is a sum of those positive balances. So my (mistaken) assumption was that the total is a calculation of the sum of rows, rather than an independant evaluation of the total. 

How would you change the formula to achieve this?

[v-qiuyu-msft]

Hi @dimitrishuk,

Please clarify about this "The outcome I am looking for is a sum of those positive balances. So my (mistaken) assumption was that the total is a calculation of the sum of rows, rather than an independant evaluation of the total. ". 

Best Regards,
Qiuyun Yu 

[dimitrishuk]

Hi Qiuyun , thanks for your reply.

The result I am looking for in the Grand Total for the column is the sum of the values of the rows - after if conditions have been applied.

Not recalculated without the context filters ie the address number

Does this make sense?

[v-qiuyu-msft]

Hi @dimitrishuk,

I still can't access the link. You can upload it to your Dropbox and share the link to everyone and paste the link here. 

Best Regards,
Qiuyun Yu 

[dimitrishuk]

 Hi @v-qiuyu-msft I have shared the file again. The link should work now.

[v-qiuyu-msft]

Hi @dimitrishuk,

I'm not able to access and download your pbix file. Please share it again. 

Best Regards,
Qiuyun Yu