Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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