Total Orders Created and Closed on the same day

[Boycie92]

Hi,

I am hoping someone can help.

I am using Direct Query and I have 3 tables. FactOrders, DimOrders and DimDate

I have a few fields in my Fact tabled three of them are DateOrderCreated, DateOrderedClosed and TargetClosedDate

My OrderID is in the DimOrder table

I wanted to be able to create two measures one that will count the number of distinct Order ID’s that were created on a certain date and one to count the number of distinct orders that are closed on a certain date

I then want to plot them on a chart using DateOrderCreated (So I don’t lose any orders)

I created this custom column:

Closed within target = IF(FactOrders [DateOrderClosed] <> BLANK() && FactOrders [DateOrderClosed] <= FacOrders[TargetCloseDate] 0, 1)

but what it does is looks to see if for that record was the order was open and closed on the same date. Which means I have missing figures as I want to be able to see all orders closed and all orders open on a certain date.

Thanks,

Boycie92

[amitchandak · Accepted solution]

@Boycie92 , You have join both the dates to date table. One will active and another one will inactive. You can use userelation to activate the inactive join.

refer

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...