cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Aging Analysis for different day range

Hello All,

I have Order table, I want generate to generate aging analysis. Order table has following fields

1. Order No.

2. Order creation Date

3. To be Approval Date

4. Approval Date

5. To be Deploy Date

6. To be Offline Date

I have created following measure and calculating days, which are working correctlu

ToApprove = Approval Date - To be Approval Date
ToDeploy = Approval Date - To be Deploy Date
ToOffline = Approval Date - To be Offline Date

Now I want to generate in following format.  In following table I want to show the numbers of the orders for each measure in different range.

Please let me know how can I achieve this kind of report.

Regards

Avian

1 ACCEPTED SOLUTION
Super User

Hi @Avian65 .
This is the steps of a simple segmentation :
1. Add a column with Dax like :

Time of execution = switch (true(),
[Days]>=0 && [Days]<=30 , "0-30",
[Days]>=31 && [Days]<=60 , "31-60",
[Days]>=61 && [Days]<=90 , "61-90",
[Days]>=91 && [Days]<=120 , "91-120",
[Days]>=121 ,"121+")

2. Add a column for the sort order of your time execution on the visuals:

rank to sort = switch (true(),
[Days]>=0 && [Days]<=30 ,1,
[Days]>=31 && [Days]<=60 , 2,
[Days]>=61 && [Days]<=90 , 3,
[Days]>=91 && [Days]<=120 , 4,
[Days]>=121 ,5)

3. Modify sort order:

4. Put all you need on a matrix:

There were some mistakes in your data, and I did not correct them.
Just wanted to show you how it works.

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

Regards,
Rita Fainshtein | Microsoft MVP
12 REPLIES 12
Super User

Hi @avi081265
You can add your calculations as calculated columns and then create simple bins for them.

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

Regards,
Rita Fainshtein | Microsoft MVP
Helper III
Hello Ritaf1983, I already saw this url. If you compare url you provided and my requirement, both are two different requirement. If any sample which is just like my requirement, it will be very useful for me. Avian
Super User

Hi @avi081265  the content of url is only a part of segmentation.

Please provide some sample data and i will show you how you can get yours desired table.

Regards,
Rita Fainshtein | Microsoft MVP
Helper III

Hello

 Order No Status TO Be Approved Date Approval Date Deploy Date Offline Date Days Formula ABCD002 Approved 4/21/2023 6/12/2023 52 Approval-To Be approved ABCD0021 Approved 5/21/2023 5/25/2023 4 Approval-To Be approved ABCD0031 Approved 5/21/2023 5/29/2023 8 Approval-To Be approved ABCD0032 Approved 4/21/2023 9/12/2023 144 Approval-To Be approved ABCD003 Deploy 3/3/2023 5/5/2023 6/1/2023 27 Deploy-Approve Date ABCD004 Deploy 3/3/2023 5/5/2023 6/20/2023 46 Deploy-Approve Date ABCD007 Offiline 4/21/2023 6/12/2023 7/1/2023 11 Offline - Approval Date ABCD008 Offline 4/2/2023 5/3/2023 7/12/2023 70 Offline - Approval Date ABCD009 Offline 4/2/2023 5/3/2023 7/12/2023 70 Offline - Approval Date ABCD009 Offline 4/2/2023 5/3/2023 9/12/2023 132 Offline - Approval Date

Avi

Super User

@avi081265

Save it on onedrive and just share the link.

But i highly recommend to share it here. This will give you opportunity to see more suggestions to solution

Regards,
Rita Fainshtein | Microsoft MVP
Helper III

Hello

I try to share Onedrive url, not able to share it, so I senr email to you.

I also added Sample file with some test account. in next thred.

Avian

Helper I
Super User

Hi @Avian65 .
This is the steps of a simple segmentation :
1. Add a column with Dax like :

Time of execution = switch (true(),
[Days]>=0 && [Days]<=30 , "0-30",
[Days]>=31 && [Days]<=60 , "31-60",
[Days]>=61 && [Days]<=90 , "61-90",
[Days]>=91 && [Days]<=120 , "91-120",
[Days]>=121 ,"121+")

2. Add a column for the sort order of your time execution on the visuals:

rank to sort = switch (true(),
[Days]>=0 && [Days]<=30 ,1,
[Days]>=31 && [Days]<=60 , 2,
[Days]>=61 && [Days]<=90 , 3,
[Days]>=91 && [Days]<=120 , 4,
[Days]>=121 ,5)

3. Modify sort order:

4. Put all you need on a matrix:

There were some mistakes in your data, and I did not correct them.
Just wanted to show you how it works.

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

Regards,
Rita Fainshtein | Microsoft MVP
Helper III

Hello,

Thanks you very much for guidance and support.

However as I mentioned in my original thread, that I have created following measure and calculating days, which are working correctl

ToApprove = Approval Date - To be Approval Date
ToDeploy = Approval Date - To be Deploy Date
ToOffline = Approval Date - To be Offline Date

want to generate in following format. In following table I want to show the numbers of the orders for each measure in different range.  Is this possible to display following matrix using 3 different measure or may be using calculated column instead of status

Avian

Super User

Hi again @avi081265 .
First of all, happy to help 🙂
While it is possible, the process is complex and necessitates the use of virtual tables. Although in most scenarios, using measures is recommended over calculated columns. In this case, the potential benefits don't appear to justify the investment. This holds true particularly when dealing with a data source like SQL or another query, as opposed to Excel. To ensure that all calculated columns are optimized, it's advisable to perform the calculations in advance and import them into POWER BI already prepared.

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

Regards,
Rita Fainshtein | Microsoft MVP
Helper III

Thank you very much for help. Can you please suggest any other sample on requirement as I mentioned earlier?

Regards

Avian

Super User

I apologize, but I have already provided a simple, dynamic, and effective solution including a sample file.
Unfortunately, I don't have time for the complex one.

Regards,
Rita Fainshtein | Microsoft MVP

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors