Forum Discussion
Using a CALCULATE measure to aggregate with dynamic dates
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 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
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!
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.
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.