IF with measure

[o59393]

Hi all

I am trying to create an if measure like this:

IF(
    HASONEVALUE(facilities[bottler])="ABI El Salvador",
    CALCULATE(
        [Complaints],
        'date'[MM YYYY]=DATE(2020,11,1),'date'[MM YYYY]=DATE(2020,12,1)),
        CALCULATE([Complaints],
        PREVIOUSYEAR('date'[date]))
)

I am using hasonvalue, followed by the column "bottler" equals to ABI El Salvador.

The measure seems to work, but the visual is:

Any idea how to get it right?

Thanks.

[HarishKM]

@o59393  Hey ,

Use this one 

IF(
SELECTEDVALUE(facilities[bottler])="ABI El Salvador",
CALCULATE(
[Complaints],
'date'[MM YYYY]=DATE(2020,11,1),'date'[MM YYYY]=DATE(2020,12,1)),
CALCULATE([Complaints],
PREVIOUSYEAR('date'[date]))

Try this and let me know .

or try this .

Calculate(calculate([Complaints],sameperiodlastyear(date[date]) , facilities[bottler])="ABI El Salvador" ) 

[o59393]

hi @HarishKM 

In fact I had tried that one. But returns a blank only.

I guess it should be solved by some kind of time intelligence formula.

THanks

[d_gosbell]

Try using the SELECTEDVALUE function instead of HASONEVALUE

[o59393]

Hi @d_gosbell 

It worked however it shows blank and it shouldn't 😞

Do you if it's possible to tell the time intelligence function "previous year" to calculate only the last 2 months?

In this case, calculate the previous year's last 2 months (november 2020 and december 2020)

The dax is below:

CC PY =

IF(

SELECTEDVALUE(facilities[bottler])="ABI El Salvador",

CALCULATE(

[Complaints],

PREVIOUSYEAR('date'[date])), -- this one to calculate last 2 months 

CALCULATE([Complaints],

PREVIOUSYEAR('date'[date]))

)

Thanks!

[Ashish_Mathur]

Hi,

Does this measure work?

CC PY = IF(SELECTEDVALUE(facilities[bottler])="ABI El Salvador",CALCULATE([Complaints],

DaTESBETWEEN('date'[date],edate(min('date'[date]),-1),max('date'[date]))),CALCULATE([Complaints],PREVIOUSYEAR('date'[date])))

[o59393]

Hi @Ashish_Mathur 

I didn't. Let me give you an example:

Let's say the yellow cell is a date slicer. Since the date contains present year, the measure should calculate the previous year for november 2020 and december 2020 if "ABI El Salvador" is selected.

There must be something wrong in the red part of the DAX:

CC PY =

IF(

SELECTEDVALUE(facilities[bottler])="ABI El Salvador",

CALCULATE([Complaints],

DATESBETWEEN('date'[date],

edate(

min('date'[date]),-1),

max('date'[date]))),

CALCULATE([Complaints],

PREVIOUSYEAR('date'[date])

)

)

I attach the excel. https://1drv.ms/x/s!ApgeWwGTKtFdh0Qw3qTRuW4VTI8D?e=ZidRvL

Let me know if you have questions.

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

=

IF(SELECTEDVALUE(facilities[bottler])="ABI El Salvador",CALCULATE([Complaints],

DATESBETWEEN('date'[date],date(year(min('date'[date]))-1,11,1),date(year(min('date'[date]))-1,12,31))),CALCULATE([Complaints],PREVIOUSYEAR('date'[date])))

[o59393]

Awsome @Ashish_Mathur 

Thanks a million all of you, have a great weekend.

[Ashish_Mathur]

You are welcome.