Running Total subtracting sum of all prior months from current month

[alicek]

Hello, 

I would like to calculate the total number NEW projects needed at any future month. This number is based on how many hours a staff person has available. However in a later month, those hours should be less than it appears, because we want to assume we've "removed" those hours by assigning them a project. 

Let's say each project is 50 hours (and lasts for many, many months). So I want a user to be able to choose Dates Between February 2025 and June 2025 to see how many new projects we would need in May 2025 and how many more, after we solved everyone in May, we would need in June 2025.

Staff	Month-Year	Hours Available (hard coded)	New Projects (calculated column based on hours)	What I want!
A	Feb 2025	5	0	0
A	March 2025	40	0	0
A	April 2025	50	1	1
A	May 2025	100	2	1
A	June 2025	100	2	0

I cannot sum the total of the New Project column. In April, I need one new project. In May, I also only need one new project!! And in June I would need zero new projects!! 

So I need a way to calculate a value for each month that substracts the value from the prior month(s). 

I figured out a way to do this using visual calucations, but those can't get totals (they get it right in each row, but no total). I'd like to do it in normal DAX measures, not visual calculations. 

Thoughts?

I was trying to get there with something like this, but I don't think it's right. 

VAR currentmonth = MONTH(SELECTEDVALUE('Reference: Date Table'[Date]))
VAR currentyear = YEAR(SELECTEDVALUE('Reference: Date Table'[Date])) 
RETURN

SUM([New Projects]) - 
CALCULATE(SUM([New Projects]), FILTER('Reference: Date Table', 'Reference: Date Table'[Year] = currentyear && 'Reference: Date Table'[Month] < currentmonth))

[rajendraongole1]

Hi @alicek  - Please check the below measure and snapshot.  replace with your table name

NewProjectsMeasure =

VAR CurrentMonth = SELECTEDVALUE('hva'[Month-Year])

VAR StaffMember = SELECTEDVALUE('hva'[Staff])

VAR ProjectHours = 50

-- Calculate total projects needed for the current month

VAR TotalProjectsNeeded =

    CALCULATE(

        SUM('hva'[Hours Available]) / ProjectHours,

        'hva'[Month-Year] = CurrentMonth

    )

-- Calculate cumulative projects assigned in previous months

VAR CumulativeProjectsAssigned =

    CALCULATE(

        SUMX('hva', SUM('hva'[Hours Available]) / ProjectHours),

        'hva'[Staff] = StaffMember,

        'hva'[Month-Year] < CurrentMonth

    )

-- New projects needed after subtracting cumulative assigned projects

RETURN

    MAX(0, INT(TotalProjectsNeeded) - INT(CumulativeProjectsAssigned))

Hope it works. 

[alicek]

Hi @rajendraongole1 - thank you for your response! 

Unfortunately, it is not recognizing Staff name or the Date field, providing only a total.  Screenshot below:

Thoughts on how to make sure it is resonsive to filter context of staff and date?

***NewProjectsMeasure = 

VAR CurrentMonth = SELECTEDVALUE('Reference: Date Table'[YearMonthShort])
VAR StaffMember = SELECTEDVALUE('Bookings per Month Totals'[Staff])
VAR ProjectHours = 50

-- Calculate total projects needed for the current month
VAR TotalProjectsNeeded =
    CALCULATE(
        SUM('Bookings per Month Totals'[Hours Available_NoNegatives]) / ProjectHours,
        'Reference: Date Table'[YearMonthShort] = CurrentMonth
    )

-- Calculate cumulative projects assigned in previous months
VAR CumulativeProjectsAssigned =
    CALCULATE(
        SUMX('Bookings per Month Totals', SUM('Bookings per Month Totals'[Hours Available_NoNegatives]) / ProjectHours),
        'Bookings per Month Totals'[Staff] = StaffMember,
        'Reference: Date Table'[YearMonthShort] < CurrentMonth
    )

-- New projects needed after subtracting cumulative assigned projects
RETURN
    MAX(0, INT(TotalProjectsNeeded) - INT(CumulativeProjectsAssigned))

Alternatively, I change the Date fields to be the YEAR(Date) and MONTH(Date). The table no longer was responsive to the date slider/filter, and when I manually filtered the table itself, I got all zeros. Screenshot below. 

[Anonymous]

Hi @alicek ,

Base on your description, it seems can't get the correct culmulative values. Could you please provide the following info for further troubleshooting and giving a suitable soluiotn?

• Provide some sample data in the table 'Bookings per Month Totals' and 'Reference: Date Table'(exclude the sentive info) How to provide sample data in the Power BI Forum
• Is there any relationship created between the above 2 tables?
• The fields settings of your matrix visual(Columns: ?   Rows:?   Values: ?)

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards