Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
dimitrishuk
Helper I
Helper I

DAX measure using variables is displaying column total as 0

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

4 REPLIES 4
v-danhe-msft
Employee
Employee

Hi @dimitrishuk,

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

 

Regards,

Daniel He

 

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

Capture.PNG

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

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors