Using a CALCULATE measure to aggregate with dynamic dates

[powerbr]

Hi all,

When I started working on this I thought it would be simple, however, it is taking a toll on me.

This is my problem:

I have this data-model, which combines three tables.

I am trying to cut the data base on some dates contained in other tables, more specifically, I want to cut FactProjExp with a selected value from DimInstance_Project[CutDate]:

Note that within PowerBI I am forcing the selection of only one instance.

The simple formula I am using is:

FYBudgetExpenses = 
    
    var ExpensesCutDate = SELECTEDVALUE(DimInstance_Projects[CutDate])   

    var Actuals = CALCULATE(
        SUM(FactProjExp[Amount]), FactProjExp[FactDate] < ExpensesCutDate)
               
    return Actuals

However, this is bringing in no data.

If I manually set-up the date it works though, I just can get my head around on why this is happening or if there is any unintended relation.

Many thanks!

[AllisonKennedy]

@powerbr  You're getting no results because the DAX you're using conflicts with the relationships. You either need to delete the relationship between the two tables DimInstanceProj and FactProjExp or use ALL or ALLSELECTED. 

Not sure if this post will help you much as you're almost there, but similar to what's being done with approximate lookups: https://excelwithallison.blogspot.com/2020/06/dax-approximate-lookup.html 

[powerbr]

Hi Allison, thanks for the reply.

I did try this:

FYBudgetExpenses = 
    
    var ExpensesCutDate = SELECTEDVALUE(DimInstance_Projects[CutDate])   

    var Actuals = CALCULATE(
        SUM(FactProjExp[Amount]), 
        FILTER(ALL(FactProjExp[FactDate]), FactProjExp[FactDate] < ExpensesCutDate))
               
    return Actuals

But it still didn't show any result.

I did created a model w/out the relations but I still got the same error. I think I am doing things on a non-PBI way and trying to fit a different reasoning, who knows.

Now I am just adjusting the query in the back, but I guess this means my data will grow exponentially.

I will try the approach from your blog one more time, maybe I missed something, and come back to you. Thanks again!

[v-stephen-msft · Accepted solution]

Hi @powerbr ,

If you want to use SELECTEDVALUE(DimInstance_Projects[CutDate]) as the filter result of the slicer, there must be no relationship between your DimInstance_Projects table and the FactProjExp table.

If you want to add up the values on April 5, 2021, you can add the equal sign.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[powerbr]

Thanks, this works.

I am marking this down as a solution.

I have a doubt, if I create a relationship but it only filters one way (Ideally, I want to filter those tables as well based on dates), will this have an impact?
I am going to be trying this.

Doing this task made me learn lots about PBI, much appreciated.