Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Solved! Go to 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 ) )
Hello @lpimentel
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 ) )
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:
I beleive hte problem is in setting the starting date and the ending date
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 ) )
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |