Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello,
I know this is a simple solution, but after reviewing and testing multiple potential solutions - still not quite there.
Wanting to calculate the time interval buckets for an AR/AP aging report. I have a measure that appears to be working correctly at the invoice level, but when summing to the vendor or grand total level, I have a blank.
This is more most recent version of measure
AgingBucket >0 =
Var Days = 0
//VAR MinBucket = [InvoiceDueDateM]+Days
//Var MaxBucket = minbucket+30
Var VendorTotal = sumx(VALUES(TADConsolidated[Vendor]),
VAR MinBucket = [InvoiceDueDateM]+Days
Var MaxBucket = minbucket+30
Return SWITCH(TRUE(),
[MaxDate]>MinBucket&&[MaxDate]<=MaxBucket,[Net Invoice Due TEST],
BLANK()))
Return VendorTotal
I have also attempted a calculate with a filter for data brackets, but get the error - SumX does not allow for boolean values, thus I found a reference of using a switch statement to work around.
I truly appreciate the Forum's generosity!
@Dellis81 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8
@Greg_Deckler , thank you for responding and links.
I have seen your posts in prior efforts, and agree - wrong totals are a real challenge. I had even voted for for this idea (provided link) sometime ago. Good to know there are potential solutions coming.
Unfortunately, I am still not getting your proposed measure working. The results still pulls a blank. (I realize I still need to do the if onevalue component, but thought I would at least get the totals to pull in)
AgingBucket >0 Test =
Var calcTable = SUMMARIZE(TADConsolidated,[Vendor],"__value",[AgingBucket >0])
Var VendorTotal1 = sumx(calcTable,[__value])
Return
VendorTotal1
Here's my "single measure" in your terminology. Note, I have attempted to use a switch and an if statement to see if that helps in the totals calculates. Both methods return the same result.
AgingBucket >0 =
Var Days = 0
VAR MinBucket = [InvoiceDueDateM]+Days
Var MaxBucket = minbucket+30
Var SingleM =
SWITCH(TRUE(),
[MaxDate]>MinBucket&&[MaxDate]<=MaxBucket,[Net Invoice Due TEST],
BLANK())
Var SingleM2 = if([MaxDate]>MinBucket&&[MaxDate]<=MaxBucket,[Net Invoice Due TEST],
BLANK())
Return
SingleM
//SingleM2
Thank you!
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
105 | |
99 | |
98 | |
38 | |
37 |
User | Count |
---|---|
152 | |
120 | |
73 | |
72 | |
63 |