As of aging bucket

[marcuschuah]

Hi,

I looking to create as of aging report, but have difficulty on getting on the age bucket based on as of date.

Example, when select Oct 2017, age bucket will show last 30, 60, and 90 days from Oct 2017. When select July, is from July. -30 days, -60days, -90 days.

Please assist.

Thanks

[v-yulgu-msft]

Hi @marcuschuah,

You need a date table (suppose it's named as 'DimDate'), and add [date] column from this date table into slicer. You could add such a filter in your calculation:

Result=CALCULATE(expression, FILTER(TableName, TableName[Date]>=DateAdd(MAX(DimDate[date]),-60,day) && TableName[Date]<=DateAdd(MAX(DimDate[date]),-30,day) ))

For more advice, please provide sample data.

Regards,
Yuliana Gu

[marcuschuah]

Hi @v-yulgu-msft,

Should it be to calculate the age 1st? Like how to get 1-30 days & 31-60 days? As the entries date will be define depend on month selection. The month will check the aging bucket.

My table consist of

Transaction & DueDate    Customer         Type               Amount

1/9/2017                          PowerBI         Invoice              1000

2/9/2017                          PowerBI         Payment          -1000

1/10/2017                        PowerBI         Invoice              2000

1/11/2017                        PowerBI         Invoice              1000

Expected to return

If i run the select as of date of end of Septemeber

Customer             1-30           31-60        Not Due

PowerBI                 0                     0             3000

If on end of Oct

Customer             1-30           31-60        Not Due

PowerBI                2000                              1000

If on end of Nov

Customer             1-30           31-60        Not Due

PowerBI                1000           2000             0

[Omega]

Assuming you have a column for dates, try creating the following columns:

Last 30 days= DateAdd(Date,-30,day)

Last 60 days= DateAdd(Date,-60,day)

Last 90 days= DateAdd(Date,-90,day)

[marcuschuah]

Using the formula, last 60 days will also return 30 days record, same for 90 days. The bucket should be view as

1-30days | 31 Days - 60 days | 61 days - 90 days. 

Before adding the bucket, i think need to calculate which line is refer to the bucket? 

for example;

Date               Name       Amount

23/10/2017    Marcus    $100

If I run as of September. No record will show.

If run on Nov,  $100 will be on 1-30days

If run on Dec,  $100 will be on 31-days