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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
watje255_ju
Helper II
Helper II

Calculate totals based on switch true groupings

Hello, 

I have the following formula which categorises aging of receivables based on the overdue days

 

Aging Buckets = SWITCH(TRUE(),
AND([Days Over] =0, [Days Over]>=0), "Current",
AND([Days Over]>=1, [Days Over]<=30), "Age 1 -30 Days",
AND([Days Over]>=31, [Days Over]<=60), "Age 31 -60 Days",
AND([Days Over]>=61, [Days Over]<=90), " Age 61 -90 Days",
"Age 90 Days +"
 

I would then like to calculate measures for  the total amount for each bucket using some logic, for example, to calculate the total for all invoices with less than 31 days overdue, but I get errors

watje255_ju_0-1656674450426.png

 

The end goal would to have measures for total overdue, 30 60 days overdue, 90 days overdue etc that i can easily change depending on the range overdue days we want to look at.  Thanks for your help!
1 ACCEPTED SOLUTION

@watje255_ju 
Yes you can. Please refer to the original reply updated as requested. Have a good day!

View solution in original post

11 REPLIES 11
tamerj1
Super User
Super User

Hi @watje255_ju 

would you please share a screenshot of your visual?

Hi @tamerj1

Thanks also for your help, here is the current table, you can see the buckets from the switch true formula, but ideally i would be able to create measures off the buckets or Days Over formula, e.g. a measure that calculates the sum of either all invoices aged over 31 days, or the sum of the buckets 

 Age 61 -90 Days",
"Age 90 Days +" etc. 
 
My days over formula is dynamic based on the selected reporting day 
Days Over =
IF( AND( [Invoice Date] < [Selected Date], [Due Date] < [Selected Date] ),
DATEDIFF( [Due Date], [Selected Date], DAY ),
0 )
watje255_ju_0-1656684003028.png

 

Is there anywhere to load up the test PBIX file in this forum it might be helpful? Thanks again!

 

@watje255_ju 

You can upload and share a download link. I believe there is a better approach to this sibject. I will look into it and get back to you by tomorrow morning. Thank you

Hi @tamerj1  https://1drv.ms/u/s!As8wMr9_bgGrgV0CJ2U8MflBJ8nU?e=CB6GaJ here is the PBIX link - thanks again for your help. let me know if this doesnt work! Cheers

Hi @watje255_ju 
Here is the sample file with the solution https://we.tl/t-FpKkxGhFRd

First you need to to create a filter table containing the buckets and the ranges.

1.png

 

 

Buckets = 
SELECTCOLUMNS (
    { 
        ( "Current", 0, 1 ), ( "Age 1 -30 Days", 1, 30 ), 
        ( "Age 31 -60 Days", 30, 60 ), ( "Age 61 -90 Days", 60, 90 ), 
        ( "Age 90 Days +", 90, 9999999 ) 
    }, 
    "Bucket", [Value1],
    "From", [Value2],
    "To", [Value3]
)

 

 

Then use this version of the Days Over measure "[Days Over 2]"

2.png

 

 

Days Over 2 = 
VAR SelectedDate = [Selected Date]
RETURN 
    SUMX ( 
        VALUES ( FactAccountsReceivable[Voucher] ),
        VAR InvoiceDate = CALCULATE ( MIN ( FactAccountsReceivable[Document Date] ) )
        VAR DueDate = CALCULATE ( MIN ( FactAccountsReceivable[Due Date] ) )
        RETURN
            IF ( 
                InvoiceDate < SelectedDate
                    && DueDate < SelectedDate,
                DATEDIFF ( DueDate, SelectedDate, DAY ),
                0
            )
    )

 

 

Finally create the Amount per Bucket measure that returns the Amount based on the Buket and the selected date.

1.png

 

Amount Per Bucket = 
SUMX ( 
    VALUES ( Buckets[Bucket] ),
    CALCULATE (
        VAR FromDays = SELECTEDVALUE ( Buckets[From] )
        VAR ToDays = SELECTEDVALUE ( Buckets[To] )
        VAR Result =
            CALCULATE (
                [Amount],
                FILTER ( 
                    VALUES ( FactAccountsReceivable[Voucher] ),
                    VAR DaysOver = [Days Over 2]
                    RETURN
                        DaysOver > FromDays
                            && DaysOver <= ToDays
                )
            )
        RETURN
            Result
    )
)

 

 

 

 

Hi @tamerj1 , this is awesome, thank you very much! appreciate the detailed answer breaking out the logic - and the buckets filter table is an awesome way to be able to group the aging buckets as the manager wants which is perfect. Is there anyway to get the grand total to calculate at all? 

watje255_ju_0-1656981832538.png

 

Hi @tamerj1 , that's working perfectly, thank you very much! Really appreciate your help!

@watje255_ju 
Yes you can. Please refer to the original reply updated as requested. Have a good day!

AminAlian
Frequent Visitor

Hello @watje255_ju !

 

As you create the "Aging Buckets" column, it will be filled with strings. Later in you IF(), you compare the "Aging Buckets" to numbers. This will cause an error. Try doing 

Less than 31 Days = CALCULATE('Aging Measures'[Amount], IF('Aging Measures'[Days Over], [Amount], 0))

Hi Amin, 

 

Thanks so much for your reply! I still get the same error "A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed." Is there a way to upload the PBIX file here? Much appreciated!

 

sorry forgot to tag @AminAlian 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors