Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. 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 |
---|---|
76 | |
76 | |
56 | |
38 | |
34 |
User | Count |
---|---|
99 | |
56 | |
51 | |
44 | |
40 |