Bug with IF statement?

[Newton]

I have two pivot tables, both use the same dimension (row) and measure.
However, the first one shows values for the current year (year that is selected in the filter pane), and the second one should show values for the year before that.

So, inn the second table, I set (using the set analysis below) to use the year before the selected one and ignore the Months field. it works.

Year Field = {$(=Max([Year Field]-1))},
Month Field =

 https://i.imgur.com/o5c0eON.png

The problem is:

I need to display each "Indicator" in a different number formatting, so I make an IF:

IF Indicator 1 = Num([Measure], '#.##0') (and so on)

and suddenly it stop works properly:

https://i.imgur.com/XEpJtUj.png

Any suggestions?

[Newton]

For the selected year, like in yours, it's works fine, the problem is with the selected Year-1:

https://i.imgur.com/z2jPqej.png

Selected year expression:

Sum([Fact Table.Value])

Year-1 expression:

If([Fact Table.Code] = 1, Sum({<
[Data de Contabilização.Ano] = {$(=Max([Data de Contabilização.Ano]-1))},
[Data de Contabilização.Mês Abreviado Ano] =
>}
[Fact Table.Value]),

If([Fact Table.Code] = 2, Num(Sum({<
[Data de Contabilização.Ano] = {$(=Max([Data de Contabilização.Ano]-1))},
[Data de Contabilização.Mês Abreviado Ano] =
>}
[Fact Table.Value]), '#.##0'),

If([Fact Table.Code] = 3, Num(Sum({<
[Data de Contabilização.Ano] = {$(=Max([Data de Contabilização.Ano]-1))},
[Data de Contabilização.Mês Abreviado Ano] =
>}
[Fact Table.Value]), '#.##0,0000')
)))

[stevejoyce · Accepted solution]

Your Code isn't returning result in the context of previous year's month.  I did what I mentioned on a previous post and it's working only({1} [Fact Table.Code]) and used pick as it's a bit simpler...

pick(only({1} [Fact Table.Code]) ,

Sum({<
Year = {$(=Max(Year-1))},
MonthYear =
>}
[Fact Table.Value])

,

Num(Sum({<
Year = {$(=Max(Year -1))},
MonthYear =
>}
[Fact Table.Value]), '#.##0')

,

Num(Sum({<
Year = {$(=Max(Year-1))},
MonthYear =
>}
[Fact Table.Value]), '#.##0,0000')

)

[Newton]

So that's how only() works! Now it's works, thank you

[salmankojar]

Hello Steve,

i was facing similar issue and i solved it with your solution(THANKYOU) which i can explain you in brief here..

i was having DIM1 dimension and a measure which calculates total amount for that dimension for last year Month till date and last Year till Date in a single measure. i used an inline for this.

My Measure Exp is Pick(Wildmatch(InlineDim,'MTD','YTD'), LYMTD Exp, LYTD Exp) against that dimension in pivot table..

i didnt have any data for a particular month in this year for example July-23..so when i select a july month in filter and No year selection is made then my last Years MTD is for July-22 which is correct..
But as soon as i select a current Year in Year Filter for july Month then instead of giving me Jul-22 calculation it gives the output of my LYTD Expression in MTD Column only.

But When i use your solution like this Pick(Wildmatch(only({1}InlineDim),'MTD','YTD'),LYMTD Exp,LYTD Exp)

then it works Fine.i  got the solution to my problem but i didnt Understand how did it worked..

Can you Please help me in understanding this that what only({1}) actually does ?