Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

  • 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.

    • Anonymous's avatar
      Anonymous
      Not 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's avatar
        lbendlin
        Icon for Super User rankSuper 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.