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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

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
Microsoft Employee
Microsoft 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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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