cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

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.

2 REPLIES 2
Syndicate_Admin
Administrator
Administrator

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/1447523

Syndicate_Admin
Administrator
Administrator

Hi @KavyaShekarappa 

 

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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Users online (1,441)