Hello dear Power BI-users,
I am preparing a dynamic Customer Aging report that segments my customers in aging buckets.
These aging buckets were created using a separate ‘aging table’ - the trick Sam explained in one of his videos. (similar to https://youtu.be/5_KDJGPjMnI )
The “Not due” bucket is causing an error for the 0-value in the Max-column.
The result is not showing anything for this column, if I add range -99999 to 0.
If I, e.g. use -99999 to 1 or -99999 to -1, then it seems to be working perfectly fine.
But this would result in missing the invoices where ‘selected date = due date’.
So please note that my ‘Days Left’-measure can have negative value or zero (if the due date >= selected date). In that case, it should be adding to the ‘Not due’ bucket.
These are my measures :
Invoice Values = IF( ISBLANK([Days Left]), BLANK(), SUM(data_AR[Amount in local currency]) ) **Due Date =** MIN(data_AR[Net due date]) **Posting Date =** MIN(data_AR[Posting date]) **Clearing date =** MIN(data_AR[Clearing date]) **Selected date =** IF( ISBLANK([Posting Date]), BLANK(), MAX('dim_Posting Dates'[Date]) ) **Days Left =** IF( AND( [Posting Date] <= [Selected date], OR([Clearing date] > [Selected date], [Clearing date] = BLANK())), DATEDIFF( [Due Date], [Selected date], DAY ), BLANK() ) **Receivables Per Group =** CALCULATE( [Invoice Values], FILTER( data_AR, COUNTROWS( FILTER( 'Aging Groups', [Days Left] >= 'Aging Groups'[Min] && [Days Left] <= 'Aging Groups'[Max] ) )))
Thank you very much for your help!