Forum Discussion
Amount spend per project until today
Hello
Sorry for my dumb question but I'm struggling with this point.
I have the following table where I have the cost per mont of my some project
| Projet | 01-02-24 | 01-03-24 | 01-04-24 | 01-05-24 | 01-06-24 | 01-07-24 | 01-08-24 | 01-09-24 | 01-10-24 | 01-11-24 | 01-12-24 |
| 1 | 100.00 | 100.00 | 100.00 | 100.00 | 100.00 | 100.00 | 100.00 | ||||
| 2 | 2000.00 | 1000.00 | 2000.00 | 2000.00 | 2000.00 | 2000.00 | 2000.00 | 2000.00 | 2000.00 | 2000.00 | 2000.00 |
| 3 | 3000.00 | 3000.00 | 3000.00 | 3000.00 | 3000.00 | 3000.00 | 3000.00 | 3000.00 | 3000.00 | 3000.00 | 3000.00 |
| 4 | 4000.00 | 4000.00 | 4000.00 | 4000.00 | 4000.00 | 4000.00 | 4000.00 | 4000.00 | 4000.00 | 4000.00 | 4000.00 |
I would like via PowerBI to have the total budget spend per project until today.
On PowerBi, I have unpivoted the table, so I now have in PowerBI :
Column 1 ==> All Project ID
Column 2 ==> All Months
Column 3 ==> Cost relative to the project and the month
I tries du create a new column with the formula :
| Projet | Month | Cost | Column |
| 3 | 2024-02 | 3000 | 12000 |
| 3 | 2024-03 | 3000 | 12000 |
| 3 | 2024-04 | 3000 | 12000 |
| 3 | 2024-05 | 3000 | 12000 |
| 3 | 2024-06 | 3000 | 12000 |
| 3 | 2024-07 | 3000 | 12000 |
| 3 | 2024-08 | 3000 | 12000 |
| 3 | 2024-09 | 3000 | 12000 |
| 3 | 2024-10 | 3000 | 12000 |
| 3 | 2024-11 | 3000 | 12000 |
| 3 | 2024-12 | 3000 | 12000 |
But for Column 2, I have a different value for the month of March, where the amount spend that month differs from the others
| Projet | Month | Cost | Column |
| 2 | 2024-02 | 2000 | 6000 |
| 2 | 2024-03 | 1000 | 1000 |
| 2 | 2024-04 | 2000 | 6000 |
| 2 | 2024-05 | 2000 | 6000 |
| 2 | 2024-06 | 2000 | 6000 |
| 2 | 2024-07 | 2000 | 6000 |
| 2 | 2024-08 | 2000 | 6000 |
| 2 | 2024-09 | 2000 | 6000 |
| 2 | 2024-10 | 2000 | 6000 |
| 2 | 2024-11 | 2000 | 6000 |
| 2 | 2024-12 | 2000 | 6000 |
Can you please tell me what I am missing?
Thanks for your help
Hi Thrimph,
I've been trying to replicate your issue and I solve it using this measure:VAR CurrentProject = 'Raw Data'[Projet] RETURN CALCULATE( SUM('Raw Data'[Cost]), FILTER( ALL('Raw Data'), 'Raw Data'[Projet] = CurrentProject && 'Raw Data'[Month] <= TODAY() ) )The final result was this:
2 Replies
_AAndradeResident Rockstar
Hi Thrimph,
I've been trying to replicate your issue and I solve it using this measure:VAR CurrentProject = 'Raw Data'[Projet] RETURN CALCULATE( SUM('Raw Data'[Cost]), FILTER( ALL('Raw Data'), 'Raw Data'[Projet] = CurrentProject && 'Raw Data'[Month] <= TODAY() ) )The final result was this:
- amitchandak
Super User
Thrimph , You need to unpivot the data shown in table 1. Then you will get date and value(after rename)
And then you can have measure like
CALCULATE(SUM('Raw Data'[Cost]),'Raw Data'[Month]<today())
of with help from date table joined with date you can build cumulative totals
Example, use your table and columns
Cumm Sales = CALCULATE(SUM('Raw Data'[Cost]),filter(all('Date'),'Date'[date] <=max('Date'[date])))
Cumm Sales = CALCULATE(SUM('Raw Data'[Cost]),filter(allselected(date),date[date] <=max(date[Date])))
Cumm Based on Date = CALCULATE(SUM('Raw Data'[Cost]),, Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))
Cumm Based on Date = CALCULATE(SUM('Raw Data'[Cost]),, Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))Running Total/ Cumulative:
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f
