Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
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!
Hi,
You may download my PBI file from here.
Hope this helps.
OMG @Ashish_Mathur, you genius! worked like a charm! Always saving the day! Thank you so much!
You are welcome. Thank you for your kind words.
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!