Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
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:
Proud to be a Super User!
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:
Proud to be a 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=42
Continue 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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
87 | |
84 | |
70 | |
49 |
User | Count |
---|---|
141 | |
120 | |
112 | |
59 | |
59 |