Forum Discussion
Calculate value per month based on different value per day
I want to show in a matrix the value per month of all projects during a selected period. This isn't very difficult, but I'm stuck with the following:
A project can have a different value during the selected period per each day that falls within it. As an example:
A project (ProjectId) has a value of € 400 per day. The project runs from 01-01-2022 to 01-05-2022. The total project value for the month of January is therefore: € 12,400 (31 days * € 400). The total project value in the month of March is € 12,400.
Project ID | Project_start | Project_end | Value_day |
1 | 01-01-2022 | 01-05-2022 | € 400 |
2 | 06-06-2022 | 01-12-2022 | € 50 |
…. | …. | …. | …. |
Now I have another table where the discounts are registered during a certain period:
ProjectId | Rebate_start | Rebate_end | Value_Day | Rebates_code |
1 | 05-01-2022 | 07-01-2022 | 200 | 800 |
1 | 08-03-2022 | 15-03-2022 | 100 | 850 |
1 | 15-04-2022 | 15-05-2022 | 0 | 856 |
2 | 06-06-2022 | 26-08-2022 | 15 | 941 |
… | … | … | … | … |
In this example, the project value for ProjectId 1 in the month of January is therefore not €12,400, but:
28 * €400 = €11,200
3 * €200 = €600
Total value January: €11,800
So not € 12,400 for the month of March, but:
23 * €400 = €9,200
8 * €100 = €800
Total value March: €10,000
I expect the following matrix as the result:
ProjectId | January - 2022 | February - 2022 | March - 2022 |
1 | € 11.800 | € 11.200 | € 10.000 |
2 | € 465 | € 420 | € 465 |
…… |
|
|
|
I’ve tried with min and max. But when I use a calender slicer only the first line is then used for the calculation.
Does anyone have an idea how I can solve this?
3 Replies
- amitchandak
Super User
Jobi10 , See if this can blog can help
Hi Amitchndak, thanks for your help. It works fine for the total value per Month without the rebates. However, with this solution it doesn't understand whitch part of a certain "Rebates" rule is active. In this case for the month of January of project 1: I expect to show a value of € 200 in the Value_month_rebates column.
I used this Measure:1. Day by Month rebates = CALCULATE(countx(SUMMARIZE(filter(CROSSJOIN('Rebates','Date'),'Date'[Date] >= 'Rebates'[Start_rebates] && 'Date'[Date] <= 'Rebates'[End_rebates]),'Project[ProjectID],'Date'[Date]),'Date'[Date]))
2. Value_Month_rebates = [Day by Month rebates] * SELECTEDVALUE(Rebates[DayRebates])
The only relation between tables is the relation Project [ProjectId] 1 ---- * Rebates [ProjectId]
There are no relations to the calender table.Do you have any other solutions to show the total rebates value per month combined with the total projectvalue?
Hi Jobi10 ,
In this case, you may need a new Calendar table with all consecutive dates.
Please provide me with more details about your table and your problem to help us clarify your scenario. Or share me with your pbix file after removing sensitive data.
Refer to:
How to Get Your Question Answered Quickly - Microsoft Power BI Community
How to provide sample data in the Power BI Forum - Microsoft Power BI Community
Best Regards,
Eyelyn Qin
