Forum Discussion
Include a date filter in a DAX sum
I have been calculating the budget variance over the lifespan of a project and have the following DAX measure
EstimateAtCompletion = SUM(Projects[ProjectRemainingCost])+
CALCULATE(
SUM(Tasks[SAPActualCost]),
NOT(NOT(Tasks[TaskIsProjectSummary]))
+0)
Where
- Projects[ProjectRemainingCost] is a sum field, direct from MSProject
- Tasks[SAPActualCost] is also from MS Project, but at the task level and I only pick up the overall ProjectSummary value
All good so far as both values report correctly when put into a table, reporting by project.
I was then able to calculate the variance from the project’s budget compared to the Remaining plus Actual costs to date
Cost Variance = Projects[OverallBudgetTotal]-Projects[EstimateAtCompletion]
Where
Projects[OverallBudgetTotal] is a is a calculated measure
OverallBudgetTotal =
CALCULATE(
SUM('Projects'[BudgetTotal]),
ALLSELECTED(Projects[BudgetTotal])
)+0
and
Projects[EstimateAtCompletion] is the measure, created above
This worked fine until the reporting requirements were changed and costs/actuals were only to be reported for the current Financial Year, starting in April.
Where I’m struggling is to calculate the variance of the Actual costs to date plus the Remaining costs against the project’s budget value.
I filter the page to get SAPActualCost incurred on or after 1st April and I think this is where my problem lies as I need to build that filter into the new FY24EstimateAtCompletion value in the Cost Variance calculation instead of taking the SAPActualCost value from the overall Project Summary.
I have a new field for the FY24 Budget, which picks up the new FY values
FY24BudgetTotal = Projects[FY24CAPEX] + Projects[FY24OPEX] + Projects[CTATotal]
+0
And am trying to replace the original variance calculation with the following
FY24EstimateAtCompletion = SUM(Projects[ProjectRemainingCost])+
CALCULATE(
SUM(Tasks[SAPActualCost]),
NOT(NOT(Tasks[TaskIsProjectSummary])) <--- needs to be replaced with a filter that picks up Tasks[SAPActualCost] on or after 1st April
+0)
What would be the correct replacement for the code that “needs to be replaced” please ?
Thanks and regards
Fred
Enough with the NOT(NOT()) silliness already.
EstimateAtCompletion = SUM(Projects[ProjectRemainingCost])+ CALCULATE(SUM(Tasks[SAPActualCost]), Tasks[TaskIsProjectSummary])FY24EstimateAtCompletion = SUM(Projects[ProjectRemainingCost])+ CALCULATE(SUM(Tasks[SAPActualCost]), Tasks[TaskDate] >= dt"2023-04-01")Easy on the +0 too - only use when absolutely needed.
3 Replies
- lbendlin
Super User
Enough with the NOT(NOT()) silliness already.
EstimateAtCompletion = SUM(Projects[ProjectRemainingCost])+ CALCULATE(SUM(Tasks[SAPActualCost]), Tasks[TaskIsProjectSummary])FY24EstimateAtCompletion = SUM(Projects[ProjectRemainingCost])+ CALCULATE(SUM(Tasks[SAPActualCost]), Tasks[TaskDate] >= dt"2023-04-01")Easy on the +0 too - only use when absolutely needed.
- AnonymousNot applicable
Thanks for the feedback unsure (rather than NOT sure) why the double NOT was used, doesn't make sense.
I ended up using a FILTER on the date, which worked for me.FY24CostToDate = CALCULATE(
Sum('_Finance Raw Data'[Val/COArea Crcy]),
FILTER(
'_Finance Raw Data',
'_Finance Raw Data'[Period]>=DATE(2023,04,01)
)
)
As for the +0, I was told that forced a zero value if the input fields were blank/empty, again, works for me and never seen any adverse results.- lbendlin
Super User
If it works for you then fine but personally I prefer to see the errors/blanks (so I can fix them) rather than mask them out.