The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hey,
I am struggling writing a specific formula for finding the last day of sale before i specific date - and not just the last sales date, as i wish to track "idle" inventory development over time.
I have two tables, one with transactions and one with end of month inventory value. I need to write a new column in the financial stock value tabel which specifies the latest day of sale for a product prior to the end of month inventory date (date column in financial stock value tabel).
The dates should be extracted from the transaction tabel, date column (for sales entries), and written in the financial stock value tabel, last sale day column. Color coding shows expected output.
Excel tables attached below as well.
Any help is appreciated as this challenge is beyond my DAX knowledge.
Transaction table | |||
Product | Date | Type | value |
A | 14-05-2023 | Sales | 2 |
A | 16-05-2023 | Purchase | -2 |
A | 27-06-2023 | Sales | 2 |
B | 16-05-2023 | Sales | 1 |
B | 16-06-2023 | Sales | 1 |
B | 27-07-2023 | Sales | 1 |
C | 14-05-2023 | Purchase | -3 |
C | 16-05-2023 | Sales | 3 |
C | 27-06-2023 | Sales | 3 |
Financial Stock value tabel | ||||
Product | Date | Stock value | Last sale day | Days since last sale |
A | 31-05-2023 | 100 | 14-05-2023 | 17 |
B | 30-06-2023 | 120 | 27-06-2023 | 3 |
C | 31-07-2023 | 90 | 27-06-2023 | 34 |
A | 31-05-2023 | 100 | 16-05-2023 | 15 |
B | 30-06-2023 | 120 | 16-06-2023 | 14 |
C | 31-07-2023 | 90 | 27-07-2023 | 4 |
A | 31-05-2023 | 100 | 16-05-2023 | 15 |
B | 30-06-2023 | 120 | 27-06-2023 | 3 |
C | 31-07-2023 | 90 | 27-06-2023 | 34 |
Solved! Go to Solution.
Hi,
Write this calculated column formula
Last sale date = CALCULATE(MAX('Transaction'[Date]),FILTER('Transaction','Transaction'[Product]=EARLIER('Financial stock value'[Product ])&&'Transaction'[Type]="Sales"&&'Transaction'[Date]<=EARLIER('Financial stock value'[Date])))
Hope this helps.
Hi,
Write this calculated column formula
Last sale date = CALCULATE(MAX('Transaction'[Date]),FILTER('Transaction','Transaction'[Product]=EARLIER('Financial stock value'[Product ])&&'Transaction'[Type]="Sales"&&'Transaction'[Date]<=EARLIER('Financial stock value'[Date])))
Hope this helps.
Hi Ashish_Mathur,
Thanks, simple solution working perfectly - and helped me figure out why my approach based on ISONORAFTER wasn't fit for this issue.
Sincerely,
You are welcome.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
83 | |
64 | |
58 |
User | Count |
---|---|
248 | |
123 | |
112 | |
79 | |
78 |