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, if anyone could help it would be much appreciated!

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 follwowing 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??

[v-danhe-msft]

Hi @dimitrishuk,

Due to I could not reproduce your problem, could you please offer me the pbix file if possible?

Regards,

Daniel He

[dimitrishuk]

Hi @v-danhe-msft to illustrate the problem a little more I've added a screenshot

[dimitrishuk]

I'm going to bump up this post, as I think that I provide more information and a pbix for reproducing the issue.

[dimitrishuk]

Hi @v-danhe-msft, thanks for your response. Here is a link to the PBIX with the troublesome measure as well as sample data.