Counting rows of two seperate date columns in table x based on date table

[Aeolus]

Dear Experts,

I am struggling with what appears to be very easy: getting the correct rowcount of received date in the graph. 

- In table x i have two columns named "Process Date" and "Received Date". 

- In table y i have "Expected Delivery Date", (in the graph rowcount under "Expected GR")

- Expected delivery date and process date are linked to a date table

What i hope to see in the graph for (monday/maandag)

Inbound throughput: 13+71+8 = 92

Expected GR = 189 (From table y)

X (received date) =8 + 63 = 71 (figure can go up, should entries received on monday be processed on wednesday).

Data set drill down from year to day from date table.

Any help is much appreciated!!

[Anonymous · Accepted solution]

Hi @Aeolus ,

In you scenario, Userelationship function should help. Please check following steps as below:

1. Create inactive relationship between Received Date and Date:

2. Modify measure:

    Actual Goods Receipt = CALCULATE(COUNTROWS(ProcessData),USERELATIONSHIP('Date Table'[Date],ProcessData[Received Date]))

3. Result would be shown as below:

Hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Aeolus]

Thanks Jay,

Much appreciated: Userelationship is a beautiful addition to the formula i will use!

[Anonymous]

hi @Aeolus ,

Could you please share some example data or PBIX to me if you don't have any Confidential Information?

If so, please upload your files to One Drive and share the link here.

Best Regards,

Jay

[Aeolus]

Hi Jay,

Please see the file in attached link with some data.

https://www.dropbox.com/s/68k6dt45cuyfhni/Example.pbix?dl=0

The graph (orange bar) should mimic the data in the received date table (example week 45, monday = 67, tuesday = 6).

Thanks!