Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am trying to calculate out a rebate dollar amount, that can be calculated using a date slicer.
I have a table set up with the rebate % by month, using the first day of the month as the date. We will call this the rebate table.
Date | Month | Brand | Quarter Rebate % |
3/1/2020 | 3 | KONG | 0.05 |
2/1/2020 | 2 | KONG | 0.04 |
I have another table that purchases by date range and sku. We will call it the Purchases table.
Date | Sku | Purchases |
3/1/2020 | 1 | 20000 |
3/2/2020 | 2 | 40000 |
2/1/2020 | 3 | 10000 |
2/3/2020 | 4 | 20000 |
These tables are linked via a sku/brand linking table as well as through a general calendar table.
What I need to accomplish is the following. I need the sum of all the purchases in the purchases table for the month to be multiplied by the Quarter Rebate % for that month.
I have been able to accomplish this for a month by monty basis with the formula:
Solved! Go to Solution.
All new columns in table 2. Add additional condition as per need
max rebate date= maxx(filter(table1,month(table1[Date])=month(table2[Date])),table1[Date])
rebate =maxx(filter(table1,month(table1[Date])=month(table2[max rebate date])),table1[Quarter Rebate %])
Quarter Rebate Calc = [rebate]*[Purchases]
@amitchandak If I wanted to also have the condition by the Brand as shown in table 1, and there is a separate table with sku and brand in it, how would I add that in?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
103 | |
103 | |
87 | |
73 | |
66 |
User | Count |
---|---|
119 | |
111 | |
95 | |
79 | |
72 |