Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
Solved! Go to 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.
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")
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?
Dear v-zhangti
I used your idea with Min and Max switch and add similar columns, I successfully made it!
Thank you!
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.
Proud to be a Super User!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |