Shipped % using multiple date fields

[Anonymous]

Hi everyone,

I have two date fields (Order Date & Shipped Date).   I want to create a Shipped % field that will take the number of Orders in a month using the Shipped Date divided by the Orders in a month using the Order Date.

Any help is appreciated!

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

Hi @Ashish_Mathur ,
Unfortunately I can't share any of the data due to privacy rules but basically I would want to see that 50 orders shipped in March divided by the 150 orders that were created in March.

[selimovd]

Hey @Anonymous ,

do you also have a date table?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[Anonymous]

Hi @selimovd ,

I do have a date table, but I had to create a custom column to adjust the time zone due to a timing issue within our system for the Order Date.  When I try to use the date table, it removes the heirarchy from my custom Order Date.  Even when I select new heirarchy, it doesn't work.  I think there is something wrong with the relationship between the two tables.

 Here is my Date Table:

Dates =

ADDCOLUMNS(

CALENDARAUTO(),

"Year" , YEAR ([Date]),

"Month" , FORMAT([Date], "MMM"),

"Month Number" , MONTH([Date]),

"Quarter" , FORMAT([Date], "\QQ")

)

[selimovd · Accepted solution]

Hey @Anonymous ,

I assume the relationship is from the date table to the order date.

Try it with the following measures:

Orders = COUNTROWS( myTable )

Then for the Shipments you have to change the relationship in the measure:

Shipments =
CALCULATE(
    COUNTROWS( myTable ),
    USERELATIONSHIP ( 'Dates'[Date], 'myTable'[Shipping Date] )
)

Then for the percentage you can just divide the two measures:

Shipped % = DIVIDE( [Shipments], [Orders] )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[Anonymous]

Hi @selimovd ,

This worked great once I got my date table corrected. Thanks!

[selimovd]

@Anonymous ,

I'm happy it worked eventually 🙂 

[Anonymous]

Hey @selimovd,

I think you're suggestion is great.  

I think my problem lies with the relationship between myTable and Date Table.    This what I get when I just try to use the Date from the Date Table and the Orders from myTable:

and when I try the Shipments formula I get a message saying:

USERRELATIONSHIP function can only use the two columns references participating in relationship.

Both my dates are formatted the same.

[selimovd]

@Anonymous 

Can you post a screenshot of the relationship of these two tables?