Amount spend per project until today

[Thrimph]

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?

Thanks for your help

[_AAndrade · Accepted 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:

[amitchandak]

@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