cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marcvd
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
v-shex-msft
Community Support
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

 

10.png

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.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
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

 

10.png

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.

View solution in original post

Helpful resources

Announcements
MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors