Sumif between dates in two different tables

[Anonymous]

Hi Guys,

I need help summing the values of a second table if they are in the next 60 days of a date in the first table.

Its easiers ifi Show it:

Table 1:

SolicitingDate 

01/01/2019         

02/01/2019       

Table 2:

RedeemingDate ; TotalValue

02/01/2019     ;    10,000

02/02/2019     ;    15,000

What i want is to creat a measurement that can creat a column in the first table that is the sum of all TotalValue if the RedeemingDate is in the next 60 days. In the case aboe the Result would be:

Result

SolicitingDate ; Total Redemptions next 60D

01/01/2019     ;  25,000    

02/01/2019     ;  15,000

*All dates above are in teh following format "MM/DD/YYYY"

[jdbuchanan71]

Hello @Anonymous 

Give this a try.

Total Redemptions next 60D =
VAR StartingDate = Table1[SolicitingDate]
VAR EndingDate = StartingDate + 60
RETURN
    CALCULATE (
        SUM ( Table2[TotalValue] ),
        FILTER (
            Table2,
            Table2[RedeemingDate] >= StartingDate
            && Table2[RedeemingDate] <= EndingDate
        )
    )

[Anonymous]

Hi @jdbuchanan71 ,

Thanks for the quick answer, the measurement works, with that i mean it does not return an error. Although it doesnt return anything.

I pasted the example with only two rows but i have a wide history of dates, what I need is for the measurement to be a rolling sum of all provisioned redemptions for the next 60 days for each soliciting date.

Here is what I used:

Measure =

   VAR

     StarDate=CALCULATE(MAX('Solicitação Resgates'[DataSolicitacao].[Date]))

   VAR

      EndDate=CALCULATE(MAX('Solicitação Resgates'[D+60]))

RETURN

  CALCULATE(

     SUM('Cotizações Resgates'[Solicitação Resgates.Financeiro Ajustado]);

     FILTER(

        'Cotizações Resgates';

        'Cotizações Resgates'[DataCotizacao]>=StarDate &&

        'Cotizações Resgates'[DataCotizacao]<=EndDate

         )

    )

I beleive hte problem is in setting the starting date and the ending date

[jdbuchanan71 · Accepted solution]

It probably has something to do with where you are pulling the date fields in from and the joins in your model.  When I test this measure and pull the date in from 'Solicitação Resgates' it works.

Total Redemptions next 60D = 
VAR StartingDate = MAX('Solicitação Resgates'[DataSolicitacao])
VAR EndingDate = StartingDate + 60
RETURN
    CALCULATE (
        SUM ( 'Cotizações Resgates'[Solicitação Resgates.Financeiro Ajustado] ),
        FILTER (
            'Cotizações Resgates',
            'Cotizações Resgates'[DataCotizacao] >= StartingDate
            && 'Cotizações Resgates'[DataCotizacao] <= EndingDate
        )
    )

[Anonymous]

@jdbuchanan71 

ITS ALIVE!! JK,

Thanks very much it worked, i was pulling a datetime value, i changed to date and it worked!!

You are a genious!