Forum Discussion
conditional column wrt dates
Problem Statement:
There are 5 columns involved, which are 'Order ID', 'Order Status', 'Desired Part Delivery Date', 'In Shipping Date', 'Order Completed Date'.
I need to create a new column/measure which contains the count of 'Order ID' based on the following two conditions:
if ('In Shipping Date') IS_BLANK then check if 'Desired Part Delivery Date' >= 'Order Completed Date' and if this is true then display the count of 'Order ID'.
if ('In Shipping Date') IS_NOT_BLANK then check if 'Desired Part Delivery Date' >= 'In Shipping Date' and if this is true then display the count of 'Order ID'.
Also the 'Order ID' count should be filtered based on 'Order Status'="Completed" or 'Order Status'="Shipping"
Please help me solve this issue asap.
Hi Anonymous
You can first add a new column to flag whether an order ID meets both conditions you mentioned. Then create a measure to calcualte the count.
Create a new column as below. It returns 1 when the condition is true.
Then create a measure to count Order IDs whose Flag value is 1.
Number Of IDs = CALCULATE(COUNT('Table'[Order ID]), 'Table'[Flag] = 1)If you want to add filters, it could be
Number Of IDs 2 = CALCULATE(COUNT('Table'[Order ID]), 'Table'[Flag] = 1, 'Table'[Order Status] = "Completed")or
Number Of IDs 3 = CALCULATE(COUNT('Table'[Order ID]), 'Table'[Flag] = 1, 'Table'[Order Status] IN {"Completed", "Shipping"})Hope it helps.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
2 Replies
Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi Anonymous
You can first add a new column to flag whether an order ID meets both conditions you mentioned. Then create a measure to calcualte the count.
Create a new column as below. It returns 1 when the condition is true.
Then create a measure to count Order IDs whose Flag value is 1.
Number Of IDs = CALCULATE(COUNT('Table'[Order ID]), 'Table'[Flag] = 1)If you want to add filters, it could be
Number Of IDs 2 = CALCULATE(COUNT('Table'[Order ID]), 'Table'[Flag] = 1, 'Table'[Order Status] = "Completed")or
Number Of IDs 3 = CALCULATE(COUNT('Table'[Order ID]), 'Table'[Flag] = 1, 'Table'[Order Status] IN {"Completed", "Shipping"})Hope it helps.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
