Report on over due orders via. 30days, 90days, 180days, 1 year, 2years, over 3years

[Anonymous]

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

[Anonymous]

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"

)

[v-diye-msft · Accepted solution]

Hi @Anonymous 

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"

)

[Anonymous]

Dina Ye, thank you - I like your solution and truly appreciate all your help and support.

Best regards,

Don