Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello community!
I have a problem summing the result of two tables. The first table has the sold quantities. The second table has the forecasted quantities. Both tables are linked to a calendar table (not represented). The third table has cut-off dates for both products.
Exported:
Product | Date | Quantity |
A | 1/1/2022 | 10 |
A | 2/1/2022 | 10 |
A | 3/1/2022 | 10 |
B | 1/1/2022 | 5 |
B | 2/1/2022 | 5 |
B | 3/1/2022 | 5 |
Forecast
Product | Date | Quantity |
A | 1/1/2022 | 20 |
A | 2/1/2022 | 20 |
A | 3/1/2022 | 20 |
A | 4/1/2022 | 20 |
B | 1/1/2022 | 15 |
B | 2/1/2022 | 15 |
B | 3/1/2022 | 15 |
B | 4/1/2022 | 15 |
Cut Off Dates
Product | CutOffDate |
A | 2/1/2022 |
B | 3/1/2022 |
The first goal is to filter both tables with the cut off date, getting from the first table and the product A & B:
Product | Date | Quantity |
A | 1/1/2022 | 10 |
A | 2/1/2022 | 10 |
B | 1/1/2022 | 5 |
B | 2/1/2022 | 5 |
B | 3/1/2022 | 5 |
Those dates are <=2/1/2022 for product A (The cut off date for product A) and <=3/1/2022 for product B (The cut off date for product B).
After that I need the same for table 2, but considering the dates after the cut off date:
Product | Date | Quantity |
A | 3/1/2022 | 20 |
A | 4/1/2022 | 20 |
B | 4/1/2022 | 15 |
Next, I need to mix both tables to obtain:
Product | Date | Quantity |
A | 1/1/2022 | 10 |
A | 2/1/2022 | 10 |
A | 3/1/2022 | 20 |
A | 4/1/2022 | 20 |
B | 1/1/2022 | 5 |
B | 2/1/2022 | 5 |
B | 3/1/2022 | 5 |
B | 4/1/2022 | 15 |
Finally, my goal is to have the following result:
Date | Quantity |
1/1/2022 | 15 |
2/1/2022 | 15 |
3/1/2022 | 25 |
4/1/2022 | 35 |
I tried to do it using max for the dates, but I can't keep the Product context filter, causing that my table is only filtered by my maximum cut off date (3/1/2022)
Thanks in advance!
Solved! Go to Solution.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi @Jorrafer25
Well, this looks to me like a job for Power Query, not DAX. If you do it in PQ, you should be good to go with your reports without doing any strange acrobatics in DAX. Things will get soooooo much easier and faster...
If I get a bit of time, I'll try to write/generate the M code for this in Power Query.
User | Count |
---|---|
57 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |