Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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??
Hi @dimitrishuk,
Due to I could not reproduce your problem, could you please offer me the pbix file if possible?
Regards,
Daniel He
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
70 | |
57 | |
37 | |
36 |
User | Count |
---|---|
85 | |
65 | |
60 | |
46 | |
45 |