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         

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"

 

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

4 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super 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
        Super 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
        )
    )