Netting WIP (with aging)

[marcvd]

Hi all!

I have to make a report for the WIP with aging and do a netting with prebilled amounts.

For example:

1/ WIP of 690 and a prebilled amount of 700 => netting => 10 prebilled amount

2/ WIP of 450 and a prebilled amount of 400 => netting => 50 WIP

So far so good.

But like a said I have to make an aging table.. 😕

So in case 2:

WIP before netting

00-30 days: 50

31-60 days: 20

61-90 days: 80

... (rest): 300

Prebilled: 400

WIP after netting

00-30 days: 50

31-60 days: 0

61-90 days: 0

... (rest): 0

Prebilled: 0

Excel file with a data sample.

https://1drv.ms/x/s!Akj7bUrQd6tMxFRr7IyAuC9AHOXj?e=1mcHia

Can someone help me out?

Thanks a lot and have a wonderful weekend!

M

[Anonymous · Accepted solution]

Hi @marcvd,

I think you can create a calculated column with math functions to split/group the records based on particular intervals.

Group = 
VAR interval = 30
VAR offset =
    INT ( ( [Age] - 1 ) / interval )
RETURN
    offset * interval
        + IF ( offset > 0, 1, 0 ) & " ~ " & ( offset + 1 ) * interval

After these steps, you can create a visual based on the 'group' field as 'category' and unique 'id' field with aggregation mode 'count' to achieve your requirement.

Regards,

Xiaoxin Sheng