Measure with CALCULATE, USERELATIONSHIP and FILTER for Time Comparisons

[Asa94]

Hi all,

I hope you are all well,

I'm using a measure to count the amount of Part Numbers that were ordered after the respective Customer's Cut Off time.

Some context:

1. The table where the customer orders are stored is a Fact Table which contains the transactional data for each order with a time stamp.

2. The table with the Customer Cut off time is a Dimension Table which contains the Cut Off times per customer.

I have done some research with the CALCULATE function for the measure with the USERELATIONSHIP and FILTER. 

I seem to stumble at the FILTER function for when I need to compare the times between the 2 tables. Any help or suggestions on what I can do with regards to that?

It would be be greatly appreciated 🙂 I have attached the sample file with the Raw Data and sample output.

https://we.tl/t-f9Uv7KOqMG 

Kind Regards,

Asa

[v-yalanwu-msft]

Hi, @Asa94 ;

Sorry for my late reply. I'm not sure WHAT you mean. It would be nice to share the updated sample data and output

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @Asa94 ;

You could create a flag measure.

flag =
 var _CutOff=CALCULATE(MAX('cut off'[ZEO Cut Off]),FILTER('cut off',[Dealer Code]=MAX('Table'[Dealer Code])))
return IF( DATEDIFF(MAX('Table'[Time]),_CutOff,MINUTE)<=0,1,0)

Then apply it into table's filter.

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Asa94]

Hi @v-yalanwu-msft ,

Apologies for the late reply, have been testing the solution. 

With the flag, it gives me the correct answer, but what it does is gives all the part numbers for every customer even though there is a filter for a single customer as seen in the picture.

Kind Regards,

Asa