Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Sumif between dates in two different tables

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        ...
Need Help
Tutorial Requests
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    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
        )
    )

jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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!