cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marcp Regular Visitor
Regular Visitor

Measure counting products with test on date

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
kingcondie Frequent Visitor
Frequent Visitor

Re: Measure counting products with test on date

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

 

View solution in original post

5 REPLIES 5
kingcondie Frequent Visitor
Frequent Visitor

Re: Measure counting products with test on date

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

 

View solution in original post

marcp Regular Visitor
Regular Visitor

Re: Measure counting products with test on date

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

Super User
Super User

Re: Measure counting products with test on date

Hi,

 

Share your datasets and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
marcp Regular Visitor
Regular Visitor

Re: Measure counting products with test on date

back to work

i'll try to implement it

marcp Regular Visitor
Regular Visitor

Re: Measure counting products with test on date

Hi

Thank you very much Kingcondie !

It works fine

 

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

 

Regards 

Marc

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)