Calculate Cumulative Results

Syndicate_Admin · ‎11-28-2023

Good morning!!

At month 1, I have 3 tables:

Billing by project
Hourly cost of each employee
Hours allocated from each employee to each of the projects.

With these 3 tables, I make several measurements to calculate the final measure of the period: Result by Project ("Result 1").

In month 2, I have the same 3 tables whose values start from the day after the last day of the month 1 table. In other words, the results are not cumulative. I perform the same procedure to end up calculating the Project Result for Month 2 ("Result 2").

Now I want to see the cumulative results for all the months. The idea I have is to create a measure for every observation I have. I mean:

Cumulative Result 1 = Result 1
Cumulative Result 2 = Result 1 + Result 2
Cumulative Result 3 = Result 1 + Result 2 + Result 3
....

Is this measure-based approach correct? It seems to me that I have to calculate a lot of measurements to get to the end. Would there be any other more efficient approach or is this one okay? If I add a date column to each of the 3 tables and then attach all the tables, would I get anywhere with this approximation?

Thank you very much in advance.

Syndicate_Admin · ‎12-03-2023

Good morning @v-xiandat-msft !

Thank you for replying. I've been using power bi for 1 month now and I'm a bit lost. I attach a .pbi file and 3 Excel tables so that you can better understand my problem:

https://we.tl/t-pBP0y13NmH

As I mentioned, each month I'm going to have 3 different tables: Billing per project (PyG_220209); Hourly cost of each employee (Costehora_220209); and, Hours allocated from each employee to each project (Horas_220209).

Next month I will have the same tables with the values ranging from the 1st of that month to the 30th of the same month. That is, the tables are monthly.

Each project can be alive for several months. My goal is to know at all times the total result (accumulated of all months) of each project.

To do this, I have created the measure "RESULT FIN_220209" that collects everything that the result of each project has to have in 1 month. To get there I had to create 10 measures (I guess due to my lack of experience I haven't been as efficient as you can be and with fewer measures you could reach the END RESULT).

When I have the next month's data, I'll have to calculate a FINAL RESULT 2 for that month. And then a CUMULATIVE RESULT which will be the sum of the FINAL RESULT 220209 and the FINAL RESULT 2. And so on and so forth, having to calculate the measurements of each month plus the measure of the Cumulative Result.

I have several doubts that I summarize in two points:

Approach: I don't know if the measurement calculation approach I do is the right one. I think I'll have to calculate a lot of measurements in the end. I don't know if, for example, including a "Date" column in each table could perhaps arrive at the same result more efficiently.
Dates: Each cumulative result that I calculate is not assigned to any date. How can I assign it? Through a calendar table?

Thank you very much in advance.

Best regards.