Calculating a sum column based on two other columns using a filter

[akkitek]

Hi Everyone,
I'm trying to create a new column using DAX which sums two other columns using a filter for every month. I have a table which looks like the following:

For each month, the column should sum up "Actual Hours to Date" until that month and sum up "Person Hours Booked" for rest of the months.

So for example:
For the month of January, the formula is: (106 (Actuals Hours December)+ 160 (Actual Hours January)+ 368 (Forecasted Hours February)+ 423 (March Forecasted Hours) + 368 (April Forecasted Hours) + 423 (May Forecasted Hours)+ 405 (June Forecasted Hours)+ 167 (July Forecasted Hours))
Therefore the rate is $2420. 

For the month of Feb it should sum up actual hours until feb and person hours booked for future months.

The column should only populate until the last month where the Data type = "Actual"

Here's the expected output:

Here's the code I've tried but it doesn't seem to work. It seems like this should be easier than this:
System Rate =
IF(FACT_Fin_Data_by_Mo[Data Type] = "Actual",
VAR LastMonthsActualHours = CALCULATE(SUM(FACT_Fin_Data_by_Mo[Actual Hours to Date (Month)]),
FILTER(DIM_Date,
DIM_Date[Date] >= DATEADD(LASTDATE(DIM_Date[Date]), -2, MONTH) &&
DIM_Date[Date] <= LASTDATE(DIM_Date[Date]) &&
FACT_Fin_Data_by_Mo[Data Type] = "Actual"
)
)
RETURN LastMonthsActualHours + FACT_Fin_Data_by_Mo[Actual Hours to Date (Month)],
VAR ProjectedHours = CALCULATE(SUM(Scheduled_Variance[Person Hours Booked]),
FILTER(DIM_Date,
DIM_Date[Date] > LASTDATE(PREVIOUSMONTH(DIM_Date[Date])) &&
FACT_Fin_Data_by_Mo[Data Type] = "Projected"
)
)
RETURN ProjectedHours
)

Any help is appreciated! Thank you!

[akkitek]

Hi @Ashish_Mathur ,
I have tried a bunch of different measures but to no avail. I'm guessing its got something to do with the data model?

Here's a link to the test file: https://drive.google.com/file/d/1__IMIyQ1TU9Zo2D8U5iY4EQd8RPYzNgC/view?usp=sharing
Any ideas will be super helpful! Thank you!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[akkitek]

OMG @Ashish_Mathur, you genius! worked like a charm! Always saving the day! Thank you so much!

[Ashish_Mathur]

You are welcome.  Thank you for your kind words.

[Mahesh0016]

@akkitek Please Share your Expected output in Table formatted.
THANK YOU!!

[akkitek]

Hi @Mahesh0016 
Thank you for your reply. 
Here's the expected output in a table format:

Basically I'm looking to add the "actual hours to date" until each month and add "Person hours booked" for future months. Thanks! 

[akkitek]

@Mahesh0016  Am I doing something wrong with my Data model?