Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
83 | |
64 | |
60 | |
55 |
User | Count |
---|---|
179 | |
108 | |
105 | |
71 | |
70 |