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.
Table1. Cumulative Daily Revenue figures per Product
Product / revenue / date
Bike / 500 / 2017-01-01
Bike / 1000 / 2017-01-02
Car / 2000 / 2017-01-01
Car / 7000 / 2017-01-02
Table2. Monthly Revenue target per Product
Product / Revenue Target / Date (target)
Bike / 50000 / 2017-01-31
Car / 200000 / 2017-01-31
So, I would like to create a measure that calculates whether the Revenue from a particular product is on target, or not. The target would be calculated linearly, i.e. the revenue target for product Bike on the second of January would be 50000/31*2 = 3225.8. From Table1. we get the revenue on the 2nd of Januar was 1000, or only 31% of the targeted revenue that day.
How would you create this cross table calculation and/or the relationship between table 1 and 2, so it works for a selecter that selects based on product from table 1.
Thanks
Solved! Go to Solution.
In this scenario, you can need to add a column to show number of days in corresponding month. Then use this column calculate the cumulative target in your Revenue Table.
1. Add a NumberOfDays column in your target table.
NumberOfDays = DAY(ENDOFMONTH(Target[Date]))
2. Then you can add a cumulative target column in your Revenue table
Cumulative Target = (RELATED(Target[RevenueTarget])/RELATED(Target[NumberOfDays])*DAY(Revenue[Date]))
Regards,
In this scenario, you can need to add a column to show number of days in corresponding month. Then use this column calculate the cumulative target in your Revenue Table.
1. Add a NumberOfDays column in your target table.
NumberOfDays = DAY(ENDOFMONTH(Target[Date]))
2. Then you can add a cumulative target column in your Revenue table
Cumulative Target = (RELATED(Target[RevenueTarget])/RELATED(Target[NumberOfDays])*DAY(Revenue[Date]))
Regards,
Hi @dinovic,
checks this post I believe it's similar to what you want.
Any question please feel free to ask.
Regards,
Mfelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |