Forum Discussion

judithnig's avatar
judithnig
Regular Visitor
3 years ago

Calculate funcion with date refernce

Hello, 

I am trying to calculate the sum of new Business after a certain inflection date.

The data of the inflection dates is included in the excel as column. The new business is created as a measure.

 

I used following, but I am not sure how to reference to the inflection date, so that I get as a result only the new business after the specific inflection date.

 

Sum New BIZ after inflection date = CALCULATE(
    SUM(CRI[SUM New BIZ],
    DATE = 'Cri Transaction Detail'[Inflection Date]))

 

 

Thank you upfront, 
Judith

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi judithnig 

     

    Try using the filter function to filter the date. It should look something like this:

     

    Measure =
CALCULATE (
    [SUM New Biz],
    FILTER ( ALL ('Cri Transaction Detail'), 'Cri Transaction Detail'[Date] => (  MAX('Cri Transaction Detail'[Date])) )
)

    I used the MAX() function to get exactly one argument to filter for. You can also hard code the date, important is that you provide only one filter argument. In your example you refered to the whole colum [Inflection Date] which is why there is no exact date to filter for. 

     

    Best Regards,

    Dan 

    • judithnig's avatar
      judithnig
      Regular Visitor
      3 years ago

      Hi Dan,

       

      Thanks for your quick reply.

       

      I tried following, but I get an error. Just to clarify: my dates I want to refer to, are saved in the colum CRI Transaction Details (Inflection date), thats why I was referring to that row before. 

       

      I am still not sure how to solve this - any other idea? Not sure if below is what you had in mind. The column CRI transaction detail (date) doesnt exist like this.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        can you provide an example dataset or screenshots of the data? It's difficult to give an exact answer without knowing what the data looks like.

         

        Best regards