Measure counting products with test on date

[marcp]

Hi, I am stuck

here is my issue

i have a table1 of items with 2 columns :

item, date-ref

There is no duplicate in that table1

and a table2 of sale transactions

tr_id, date-sale, item, qty

In this table2, only tr_id is unique

Tables are linked with item field

I want to create a measure1 counting for each date-sale the number of items in table2 where date-sale>=date-ref

ultimately, I will produce a table like this

date-sale, item, measure1

any idea ?

thanks for your help

regards

Marc

[Ashish_Mathur]

Hi,

Share your datasets and show the expected result.

[marcp]

back to work

i'll try to implement it

[kingcondie · Accepted solution]

Hi marcp

I am a bit new to this, and there is probably a more straight-forward way.  I suggest the following steps:

Make a query from table 2

merge table 1 based on item

expand columns to include the ref date

Add a column that says "Keep" if the date-sale is >= ref date or "Drop" otherwise

Filter the new column to keep the "Keep" values

Group by date-sale and item, making a column called measure1 that counts rows

I think that should get you where you want to be.

kingcondie

[marcp]

Hi

Thank you very much Kingcondie !

It works fine

I can't share proprietary datas but your method solved my issue 🙂

Regards 

Marc

[marcp]

Hi Kingcondie

Thank you for your answers. This method makes sense. I’ll try it on Monday.

I wonder if a magic Dax formula can do it too.

Regards

Marc