How to limit the TOTALYTDs' to the largest month number of my table?

[Anonymous]

I am trying to limit my TOTALYTD command using this measure:

AccumPerMonth = TOTALYTD(Sum(META_X_REALIZADO[Valor]); META_X_REALIZADO[Data].[Date]; MAX(META_X_REALIZADO[Data].[MonthNo]))

But, this error message appears:

A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

How can I fix it?

[v-huizhn-msft]

Hi @Anonymous,

For TOTALYTD function, it will sum for the current year-to-date dates. You should add a steady value in your formula. You can create a measure to get the max month, then use it in formula. Your max date will be changed? Please share your data for further analysis.

IF you don't add the filter in TOTALYTD, it will calculate the sum of one whole year.


Best Regards,
Angelia

[Anonymous]

Hi @v-huizhn-msft,

I have the following simple table:

META_X_REALIZADO

    Valor (Number)

    Data (Date)

My max date will change, so I can't create a new column with a steady value.

[v-huizhn-msft]

Hi @Anonymous,

Is there any chance to post your sample data rather than table structure for analysis?

If you date have the period: from 2012/1/1 to 2012/5/3, from 2013/1/1 to 2013/8/31. You can use 12/31 in TOTALYTD function, it will calculated to the sun of first day to last day in each year.

Best Regards,
Angelia