Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Thrimph
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

 

Projet01-02-2401-03-2401-04-2401-05-2401-06-2401-07-2401-08-2401-09-2401-10-2401-11-2401-12-24
1    100.00100.00100.00100.00100.00100.00100.00
22000.001000.002000.002000.002000.002000.002000.002000.002000.002000.002000.00
33000.003000.003000.003000.003000.003000.003000.003000.003000.003000.003000.00
44000.004000.004000.004000.004000.004000.004000.004000.004000.004000.004000.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
ProjetMonthCostColumn
32024-02300012000
32024-03300012000
32024-04300012000
32024-05300012000
32024-06300012000
32024-07300012000
32024-08300012000
32024-09300012000
32024-10300012000
32024-11300012000
32024-12300012000

 

But for Column 2, I have a different value for the month of March, where the amount spend that month differs from the others

 

ProjetMonthCostColumn
22024-0220006000
22024-0310001000
22024-0420006000
22024-0520006000
22024-0620006000
22024-0720006000
22024-0820006000
22024-0920006000
22024-1020006000
22024-1120006000
22024-1220006000

 

Can you please tell me what I am missing?

 

Thanks for your help

 
 
 
1 ACCEPTED SOLUTION
_AAndrade
Super User
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:

_AAndrade_0-1715776171836.png

 





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

Proud to be a Super User!




View solution in original post

2 REPLIES 2
_AAndrade
Super User
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:

_AAndrade_0-1715776171836.png

 





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

Proud to be a Super User!




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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.