Sum for MaxDate

[Anonymous]

Hi everyone,

i have the same problem like in this thread: Show sales value for maximum date

Altough I'm using the same syntax my result is 0. I defined a variable MaxDate which shows the selected Date correctly. But in the sum if statement there's no result. When i switch MaxDate to '2018/09/20' it works fine. Thanks in advance for help.

[Gysbert_Wassenaar]

Try sum({<Berichtsdatum={'$(MaxDate)'}>} [offener errechneter Auftragswert AB])

[Anonymous]

Hi Gyspert, my result is still 0. I made a new app to solve this issue but still the same result. It works when i switch ... {'$(DateMax)'}... to '2018/09/21' for example and if I take DateMax as a stand alone Measure it is displayed correctly.

LOAD

[offener errechneter Auftragswert AB],

[Berichtsdatum]

FROM [lib://AttachedFiles/ABÜ_täglich.xlsx]

(ooxml, embedded labels, table is ABUE);

Set DateMax = Max( [Berichtsdatum] );

Set AB_akt = sum({<Berichtsdatum={'$(DateMax)'}>} [offener errechneter Auftragswert AB]);

[Gysbert_Wassenaar]

Max(Berichtsdatum) will return a number, not a formatted date. Try SET DateMax = Date(Max(Berichtsdatum),'YYYY/MM/DD');

[Anonymous]

Sorry, result is still the same. But thanks for your help.

[Gysbert_Wassenaar]

Try SET DateMax = =Date(Max(Berichtsdatum),'YYYY/MM/DD');

Or LET DateMax = '=Date(Max(Berichtsdatum),'YYYY/MM/DD')';

[Anonymous]

With  SET DateMax = =Date(Max(Berichtsdatum),'YYYY/MM/DD'); I get the result 9,34 for $DateMax and now Set AB_akt = sum({<Berichtsdatum={'$(DateMax)'}>} [offener errechneter Auftragswert AB]); shows the sum without the aggregation for $DateMax

With LET DateMax = '=Date(Max(Berichtsdatum),'YYYY/MM/DD')'; i get a failure message:

Unexpected token: 'YYYY', expected one of: 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', 'like', 'and', ...

[Gysbert_Wassenaar · Accepted solution]

Oops, yes that LET won't work with nested quotes.

9,34 = 2018/9/24. That means that the variable contains the correct value. If you use =$(MaxDate) in a text object it will be evaluated as an expression. But if you use it in a set analysis expression in quotes then '$(MaxDate)' then it will work ok. Just as when you use ='$(vMaxDate)' in a text object.