Sum where Fieldname = Fieldname

[reporting_neu]

Hello,

I have a table with data for each day. But I only ever need the last day of the month. That's why I added the field "MonthEnd(MyDate) as ypA_MonthEnd" to the table.

Now I would like to always see the value of the last day of the month.

To do this I wanted to use the formula:

SUM({$<MyDate = {"=$(=ypA_MonthEnd)"}>}Sales)

But unfortunately I don't get any result. What did i do wrong?

[SunilChauhan]

in place of EndMonth, use monthend

Try this

monthend(MyDate) as ypA_EndMonth

and 

SUM($<MyDate = {"$(ypA_EndMonth)"}>}Sales)

[reporting_neu]

Sorry, I made a mistake. I have it the way you wrote. However, unfortunately it doesn't work.
I corrected the first post above.

I get:

={}

No result found.

[TauseefKhan]

Check this:

SUM({$<MyDate = {"$(=Max(ypA_MonthEnd))"}>} Sales)

****Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.****

[SunilChauhan · Accepted solution]

try to use Number format of Date and Monthend 

Num(Date) as Numdate 

num(MonthENd(Date) as numMonthENd

or try to use in format using -   dd-mm-yyyy

[greenee]

Hi,
To only get the date (number) and not a decimal number you should use Floor(Num(MonthEnd(Date))) as Monthend_Num_Floor (change as needed)

You need to use the same format for any dates you want to do a match on or = to etc.

Data:
LOAD Date,
MonthEnd(Date) as Monthend,
Num(MonthEnd(Date)) as Monthend_Num,
Floor(Num(MonthEnd(Date))) as Monthend_Num_Floor
FROM
Date_Test.xlsx
(ooxml, embedded labels, table is Sheet1);

Regards
greenee

****Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.****

[marcus_sommer]

I suggest to create appropriate flags within the calendar, for example:

if(day(Date) = 1, -1, if(Date = floor(monthend(Date)), 1, 0)) as Flag

and then using the flag as dimension and/or selection and/or condition and/or by reducing the above to 0/1 as multiplicator and/or using dual() to add also a text to the chosen numbers.