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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Dellis81
Continued Contributor
Continued Contributor

sumX

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.   

AgingExample.PNG

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!

2 REPLIES 2
Greg_Deckler
Super User
Super User

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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

 

AgingExample2.PNG

 

 

 

 

 

 

 

 

Thank you!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.