Forum Discussion

powerbr's avatar
powerbr
Frequent Visitor
5 years ago
Solved

Using a CALCULATE measure to aggregate with dynamic dates

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!

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

4 Replies

    • powerbr's avatar
      powerbr
      Frequent Visitor
      5 years ago

      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!

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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.