dollar expansion set analysis

[Anonymous]

Hi,

I'm struggling with the syntax for a dollar expansion within set analysis.  My code is as follows;

=Sum(
 {<
     [DateType]={"Budget 2018"},
     [Measure]={"PF Units"},
     Year={2018},
     MonthNum={"<=$(=Month(=Max([Transfers.Transfer Date])))"}       
    >}
    [Budget 2018]
    )

So I am trying to calculate the month of the maximum date in the field "Transfers.Transfer Date", and use the result as part of the set analysis. 

Is my syntax correct? 

Grateful for any help!

[Gysbert_Wassenaar]

Try this:

=Sum(
{<
[DateType]={"Budget 2018"},
[Measure]={"PF Units"},
Year={2018},
MonthNum={"<=$(=Month(Max([Transfers.Transfer Date])))"}
>}
[Budget 2018]
)

[sunny_talwar · Accepted solution]

Or this

=Sum(
 {<
     [DateType] = {"Budget 2018"},
     [Measure] = {"PF Units"},
     Year = {2018},
     MonthNum = {"<=$(=Num(Month(Max([Transfers.Transfer Date]))))"}       
    >}
    [Budget 2018]
    )

[Rojarao]

=Sum(
{<
[DateType]={"Budget 2018"},
[Measure]={"PF Units"},
Year={2018},
MonthNum={"<=$(=Month(Max([Transfers.Transfer Date])))"}
>}
[Budget 2018]
)

Try this

[Anonymous]

Thanks for the responses so far. I've tried them out but still no luck. It seems as though the code I have around the formula is correct, i.e. the {"<=$(=Month at the start, and the "} at the end.

Interestingly I get the right result when I replace it with MonthNum={10} or even MonthNum={"10"}

I also know that the formula Month(Max([Transfers.Transfer Date]) returns the number 10 as I have tested that in a separate text box.

So I really don't understand why this isn't working.

Any other ideas please?

[Anonymous]

Wait, I was being daft, the solution from Stalwar1 above worked. Thank you all for your help!!