cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## 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 :

Column = CALCULATE(SUM('Raw Data'[Cost]),'Raw Data'[Month]<today())
But it did not work all the time

For Project 3, I have correct value of 12000
 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?

1 ACCEPTED SOLUTION
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()
)
)``````

Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!

2 REPLIES 2
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()
)
)``````

Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!

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:

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