Forum Discussion
Filtering DATESBETWEEN with LASTDATE
Hello,
I'm having a problem filtering this measure with the DATESBETWEEN.
How can I fix this measure, and what's the issue?
VAR DATAMAX = CALCULATE( LASTDATE(RE[Data]), RE[Informações] = "Contratos Negociados" ) VAR RESULT = CALCULATE( SUM(RE[Valor]), RE[Informações] = "Contratos Negociados", DATESBETWEEN( RE[Data], LASTDATE( DATEADD(RE[Data], -12, MONTH ) ), DATAMAX ) ) RETURN RESULT |
Table:
I need some of the last thirteen months' average that is 102.
But this measure it's returning 56 as result (it's some of the last six values).
Hi Anonymous ,
Transpose table in power query, convert the data model formed by the date column. After testing the following code, the results returned are correct. Can you provide more information or test data so that I can answer your questions as soon as possible.
sum_total = VAR DATAMAX = CALCULATE ( LASTDATE ( 'RE'[Date] ), 'RE'[Informações] = "Contratos Negociados" ) VAR RESULT = CALCULATE ( SUM ( 'RE'[Value] ), 'RE'[Informações] = "Contratos Negociados", DATESBETWEEN ( 'RE'[Date], LASTDATE ( DATEADD ( 'RE'[Date], -13, MONTH ) ), DATAMAX ) ) RETURN RESULT
Best Regards,
Stephen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous ,
Transpose table in power query, convert the data model formed by the date column. After testing the following code, the results returned are correct. Can you provide more information or test data so that I can answer your questions as soon as possible.
sum_total = VAR DATAMAX = CALCULATE ( LASTDATE ( 'RE'[Date] ), 'RE'[Informações] = "Contratos Negociados" ) VAR RESULT = CALCULATE ( SUM ( 'RE'[Value] ), 'RE'[Informações] = "Contratos Negociados", DATESBETWEEN ( 'RE'[Date], LASTDATE ( DATEADD ( 'RE'[Date], -13, MONTH ) ), DATAMAX ) ) RETURN RESULT
Best Regards,
Stephen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.