Calculating date difference from today to determine Status

[Julian_HH]

Hi all,

I have a dataset where I need to calculate and highlight jobs that has not started and has the below criteria:

- They are within 2 to 3 weeks from the [Expected Delivery Date] (EDD).
- They are within 1 week from the EDD, or doesn't have another date entered [HandIn Date]
- There are less than 80% of the [Ordered Quantity] in the [Warehouse Stock], on or after the EDD

I have tried creating a Calculated Column using DATEDIFF to subtract the EDD and TODAY() to get a number then use that to filter out my results, but no luck.

What might be other ways of getting around to do the above? I am relatively new to PowerBI, and would like to learn more about the tool. Thanks!

Julian

[v-ljerr-msft]

Hi @Julian_HH,

Could you post your table structures with some sample/dummy data and your expected result, so that we can better assist on this issue? It's better to share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[Julian_HH]

Hi @v-ljerr-msft,

Thank you for replying, here's a link to the .pbix file. I have tried filtering out non-related columns and data, hope I didn't miss anything.

To clarify, what I would like to do is to mainly work from the "Expected Delivery Date" & "Work Start Date". The conditions I want to highlight:

- If there is no "Work Start Date" 3 weeks from the "Expected Delivery Date", then mark that as Alert 1.

- If there is no "Work Start Date" or "HandIn Date" 1 week from the "Expected Delivery Date", then mark that as Alert 2.

- If there are less than 80% of the "Ordered Quantity" in the "Packed Stock" & "Warehouse Stock" on or after the "Expected Delivery Date", then mark that as Alert 3.

I would like the result to be in a table form, so that I could use conditional formatting based on the Alerts. 

Sorry if I'm not clear enough! Thanks!

Julian

[v-ljerr-msft · Accepted solution]

Hi @Julian_HH,

If I understand you correctly, you should be able to use the formulas below to add three calculate columns in your table to indicate it is Alert 1, Alert 2, and Alert 3. Then use the the three calculate columns as Slicers on your report. 

IsAlert1 = 
IF (
    Main[Expected Delivery Date] - Main[Work Start Date 1]
        > 21
        && Main[Expected Delivery Date] - Main[Work Start Date 2]
        > 21,
    1,
    0
)

IsAlert2 = 
IF (
    Main[Expected Delivery Date] - Main[Work Start Date 1]
        > 7
        && Main[Expected Delivery Date] - Main[Work Start Date 2]
        > 7
        && Main[Expected Delivery Date] - Main[HandIn Date 1]
        > 7
        && Main[Expected Delivery Date] - Main[HandIn Date 2]
        > 7,
    1,
    0
)

IsAlert3 =
IF (
    ( Main[Packed Stock] + Main[Warehouse Stock] )
        < Main[Order Quantity] * 0.8,
    1,
    0
)

Here is modified pbix file for your reference. 

Regards

[Julian_HH]

Hi @v-ljerr-msft,

This is great! Thanks a lot!