dynamic receivables aging report due date grouping

[Anonymous]

Hello BI community, i have a problem  that i hope somebody can help me with.

i need to create a histogram showing recieveables thats has been due last 12 month 

right now what works for me is calculating running sales by 

sales running  CALCULATE([Sum of Sales w. tax], FILTER(ALLSELECTED('Calendar'[Date]), 'Calendar'[Date] <= MAX('Calendar'[Date])))

and created a when due grouping column:

when due = IF('Debtor'[Due Date] > TODAY(), "Not due",
IF( AND( 'Debtor'[Due Date]<= TODAY(), 'Debtor'[Due Date] >=TODAY()- 10), "1-10 days overdue",
IF( AND( 'Debtor'[Due Date] <= TODAY()-11,, 'Debtor'[Due Date] >= TODAY() - 20), "11-20 days overdue",
IF( AND( 'Debtor'[Due Date] <= TODAY() -21,, 'Debtor'[Due Date] >=TODAY() - 30), "21-30 days overdue",
IF('Debtor'[Due Date] <= TODAY()- 30,, "30+ days overdue",
BLANK())))))

but i want to make the calculation dynamic, so it can show overdue bills based on Calendar date and not today() so it can show me bills that was overdue earlier and grouping those overdue bills into 1-10 days overdue, 11-20 days overdue, 21-30 days overdue and 30+ days overdue 

the tables looks like this at the moment:

which says that all bills is 30+ overdue because the grouping is based on today() but i want to get how much the a bill was overdue at the specific month and not how much it is overdue now 

the histogram is filtered by a year-month-day hierachy and also specifed to relative date last 12 month. 

hope somebody can help me 

[v-janeyg-msft]

Hi, @Anonymous 

It’s my pleasure to answer for you.

According to your description,If you want to make the calculation dynamic, you can only use measure because calculated column is calculated when data is refreshed or loaded .

What's connection mode of your data source?If it is LC(live connection),why can you create a column but can't create a calculated table?

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@amitchandak, thank you for your reply!

is there a way to work around the problem without adding a new table as my data is connected from Visual studio and i am not able to create a table directly in power bi?

[amitchandak]

@Anonymous , You have create a measure like, Assumed Date is selected from date table

when due =
var _today = maxx(allselected('Date', 'Date'[Date])
return
IF(max('Debtor'[Due Date]) > _today, "Not due",
IF( AND( max('Debtor'[Due Date])<= _today, max('Debtor'[Due Date]) >=_today- 10), "1-10 days overdue",
IF( AND( max('Debtor'[Due Date]) <= _today11, max('Debtor'[Due Date]) >= _today - 20), "11-20 days overdue",
IF( AND( max('Debtor'[Due Date]) <= _today -21, max('Debtor'[Due Date]) >=_today - 30), "21-30 days overdue",
IF(max('Debtor'[Due Date]) <= _today- 30,, "30+ days overdue",
BLANK())))))

You can use Switch in place of multiple if https://www.youtube.com/watch?v=gelJWktlR80

Nor create an independent table with values Not due, 1-10 days overdue etc.

Not you join this measure and table in a new measured with a group (values) of bill number (of somthing like that)

Refer my segmentation video for that

https://www.youtube.com/watch?v=CuczXPj0N-k