Calculate funcion with date refernce

[judithnig]

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

[Anonymous]

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]

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]

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