Add column to check if product sales were in a specific period

[Young_G_Han]

  Dear Kudos

I would like to add one column to check if product sales were in a previous period.

The fact table has transaction dates and there is a dim table that has many different periods.

The fact table and dim table are connected with the product code.

Take one line in the transaction table --> Check transaction date --> Find related products expired date which is earlier than the transaction date but it must be the maximum of them --> Based on the period, check sales in the transaction table --> If it has sales 'Y', if not 'N'

For example,

Fact Table

Product   Transaction date

A              2023-05-10

B              2023-05-10

A              2022-05-10

B              2023-01-05

Dim Table

Product     Act. Date       Exp. Date         Code

A               2023-01-01   2023-12-31         1

A               2022-01-01   2022-12-31         2

B               2023-01-01   2023-12-31         1

B               2022-01-01   2022-12-31         2

Result I want

Product   Transaction date   Sold in another period

A              2023-05-10                     Y

B              2023-05-10                     N

A              2022-05-10                     N

B              2023-01-05                     N

I have no idea if adding a column is better or making a summarized table is better.

Please help.

[v-zhangti · Accepted solution]

Hi, @Young_G_Han 

You can try the following methods.

Column:

Rank = RANKX(FILTER('Fact Table',[Product]=EARLIER('Fact Table'[Product])),[Transaction date],,DESC)

Act. Date = CALCULATE(MAX('Dim Table'[Act. Date]),FILTER('Dim Table',[Code]=EARLIER('Fact Table'[Rank])&&[Product]=EARLIER('Fact Table'[Product])))

Exp. Date = CALCULATE(MAX('Dim Table'[Exp. Date]),FILTER('Dim Table',[Code]=EARLIER('Fact Table'[Rank])&&[Product]=EARLIER('Fact Table'[Product])))

Previous date = MAXX(FILTER('Fact Table',[Product]=EARLIER('Fact Table'[Product])&&[Transaction date]<EARLIER('Fact Table'[Transaction date])),[Transaction date])

Column = IF([Previous date]<=[Act. Date]&&[Previous date]<>BLANK(),"Y","N")

Is this the result you expect? Please see the attached document.

Best Regards,

Community Support Team _Charlotte

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

[Young_G_Han]

  Dear danextian

Thank you for your consideration.

The period must be defined by transaction date.

Take one line in the transaction table --> Check transaction date --> Find related products expired date which is earlier than the transaction date but it must be the maximum of them --> This limits a period --> Based on the period, check sales in the transaction table --> If it has sales 'Y', if not 'N'

This means check previous sales quantity or value, but the period should be coming from the dim table.

If I can find the max expired date and activate date, can I aggregate sales between the dates in other column?

This also can be a solution. I will try this.

Thank you.

[v-zhangti · Accepted solution]

Hi, @Young_G_Han 

You can try the following methods.

Column:

Rank = RANKX(FILTER('Fact Table',[Product]=EARLIER('Fact Table'[Product])),[Transaction date],,DESC)

Act. Date = CALCULATE(MAX('Dim Table'[Act. Date]),FILTER('Dim Table',[Code]=EARLIER('Fact Table'[Rank])&&[Product]=EARLIER('Fact Table'[Product])))

Exp. Date = CALCULATE(MAX('Dim Table'[Exp. Date]),FILTER('Dim Table',[Code]=EARLIER('Fact Table'[Rank])&&[Product]=EARLIER('Fact Table'[Product])))

Previous date = MAXX(FILTER('Fact Table',[Product]=EARLIER('Fact Table'[Product])&&[Transaction date]<EARLIER('Fact Table'[Transaction date])),[Transaction date])

Column = IF([Previous date]<=[Act. Date]&&[Previous date]<>BLANK(),"Y","N")

Is this the result you expect? Please see the attached document.

Best Regards,

Community Support Team _Charlotte

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

[Young_G_Han]

 Dear v-zhangti

Your idea helps. Due to the more complicated fact table and dim table, I edited your idea and it is working!

But...

I have one more issue with this data.

I have 2 different codes 1 and 2.

I would like to add one more column that will show if code 1 product sales is in previous periods.

For example,

with the modified calculation, I could get a column shows if there is a transaction between previous periods, this is related to the code 2. Let's call it Pre_Sale

For code 1, I would like to add a column which search code 2 rows and check the product code and added column, Pre_Sale, value is 'Y'. then if there is 'Y', this added column also has 'Y', if not 'N'

Is it possible?

[v-zhangti · Accepted solution]

Hi, @Young_G_Han 

Can you show a picture of the output you expect? And give an example.

Best Regards

[Young_G_Han]

  Dear v-zhangti

I used your idea with Min and Max switch and add similar columns, I successfully made it!

Thank you!

[danextian]

Hi @Young_G_Han 

Questions:

How do you define period?

How to determine if there is a sale? Based on transaction date or the value in another column?

Also, the sample formula below calculates the expiration date based on product and which is earlier than the transaciton date but the result doesnt seem to match with your sample result.