Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Young_G_Han
Helper III
Helper III

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

  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.

2 ACCEPTED SOLUTIONS

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")

 

vzhangti_1-1701328810019.png

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.

 

View solution in original post

Hi, @Young_G_Han 

 

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

 

Best Regards

View solution in original post

6 REPLIES 6
Young_G_Han
Helper III
Helper III

  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.

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")

 

vzhangti_1-1701328810019.png

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.

 

 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?

 

Hi, @Young_G_Han 

 

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

 

Best Regards

  Dear v-zhangti

 

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

Thank you!

danextian
Super User
Super User

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.

danextian_0-1701233357434.png

 

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.