cancel
Showing results for
Did you mean:
Frequent Visitor

Netting WIP (with aging)

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

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Announcements

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.