Aging Analysis for different day range

[avi081265]

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

[Ritaf1983]

Hi @avi081265 
You can add your calculations as calculated columns and then create simple bins for them.
please refer linked tutorial:
https://www.youtube.com/watch?v=PjHgi7X3_Bw

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

[avi081265]

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

[Ritaf1983]

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.

[avi081265]

Hello

Can you share your email address, I will send sample data excel. Sorry I dont know how to attach excel file in this message.

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

[Ritaf1983]

@avi081265

Save it on onedrive and just share the link.

My mail is ritaf@madeiradata.com

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

[avi081265]

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

[Avian65]

Sample  

[Ritaf1983 · Accepted solution]

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:

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

Link to a sample file with solution 

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

[avi081265]

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

[Ritaf1983]

Hi again @avi081265 .
First of all, happy to help 🙂
Answering  your question:
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

[avi081265]

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

Regards

Avian

[Ritaf1983]

Hi @avi081265 

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.