Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Power BI Experts,
We have a collection of data and would like to report accumulated orders that has not been process over time i.e. 30days, 90days, 180days, 1 year, 2years, over 3years (see table below).
In your opinion which DAX measure formula would work best in this situation?
Thank you in advance
Example data:
Order Number | Status | Delay (days) |
CRQ000000726732 | Scheduled | 105.99 |
CRQ000000727120 | Rejected | 103.95 |
CRQ000000726876 | Scheduled | 105.97 |
CRQ000000727082 | Implementation In Progress | 108.81 |
CRQ000000729256 | Planning In Progress | 107.38 |
CRQ000000437911 | Implementation In Progress | 672.82 |
CRQ000000438854 | Request For Authorization | 654.60 |
CRQ000000439689 | Completed | 621.60 |
CRQ000000446619 | Implementation In Progress | 585.60 |
CRQ000000444950 | Rejected | 649.85 |
CRQ000000445895 | Scheduled | 621.39 |
CRQ000000445001 | Completed | 500.89 |
CRQ000000451915 | Scheduled | 623.60 |
CRQ000000453607 | Implementation In Progress | 591.64 |
CRQ000000454030 | Implementation In Progress | 546.64 |
CRQ000000451310 | Completed | 626.60 |
CRQ000000454023 | Implementation In Progress | 616.39 |
CRQ000000449555 | Scheduled | 598.60 |
CRQ000000455209 | Completed | 612.47 |
CRQ000000457510 | Completed | 602.60 |
CRQ000000455795 | Planning In Progress | 563.64 |
CRQ000000460354 | Rejected | 610.55 |
CRQ000000457410 | Implementation In Progress | 585.60 |
CRQ000000460469 | Planning In Progress | 598.60 |
CRQ000000457966 | Implementation In Progress | 585.60 |
Solved! Go to Solution.
Hi @dcg38524
if you'd like to sort the delay days by different labels as "0 - 30 Days", "30 - 90 Days"... the solution you found is a typical way.
But I made some modification on this:
Age of Delay =
SWITCH (
TRUE (),
'Table1'[Delay] >= 0
&& 'Table1'[Delay] <= 30, "0 - 30 Days",
'Table1'[Delay] > 30
&& 'Table1'[Delay] <= 90, "30 - 90 Days",
'Table1'[Delay] > 90
&& 'Current'[Delay] <= 180, "90 - 180 Days",
'Table1'[Delay] > 180
&& 'Table1'[Delay] <= 365, "180 days - 1 Year",
'Table1'[Delay] > 365
&& 'Table1'[Delay] <= 730, "1 year - 2 Years"
)
After reviewing several post within this forum I try the measure below - Let me know if there are any other solutions better than the one I selected.
Age of Delay =
SWITCH (
TRUE (),
'Table1'[Delay] >= 29
&& 'Table1'[Delay] <= 30, "0 - 30 Days",
'Table1'[Delay] > 89
&& 'Table1'[Delay] <= 90, "30 - 90 Days",
'Table1'[Delay] > 179
&& 'Current'[Delay] <= 180, "90 - 180 Days",
'Table1'[Delay] > 364
&& 'Table1'[Delay] <= 365, "180 days - 1 Year",
'Table1'[Delay] > 729
&& 'Table1'[Delay] <= 730, "1 year - 2 Years"
)
Hi @dcg38524
if you'd like to sort the delay days by different labels as "0 - 30 Days", "30 - 90 Days"... the solution you found is a typical way.
But I made some modification on this:
Age of Delay =
SWITCH (
TRUE (),
'Table1'[Delay] >= 0
&& 'Table1'[Delay] <= 30, "0 - 30 Days",
'Table1'[Delay] > 30
&& 'Table1'[Delay] <= 90, "30 - 90 Days",
'Table1'[Delay] > 90
&& 'Current'[Delay] <= 180, "90 - 180 Days",
'Table1'[Delay] > 180
&& 'Table1'[Delay] <= 365, "180 days - 1 Year",
'Table1'[Delay] > 365
&& 'Table1'[Delay] <= 730, "1 year - 2 Years"
)
Dina Ye, thank you - I like your solution and truly appreciate all your help and support.
Best regards,
Don
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |