Forum Discussion

o59393's avatar
o59393
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

IF with measure

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.

 

 

Need Help
Show and Tell
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    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])))

9 Replies

  • HarishKM's avatar
    HarishKM
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      o59393
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      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's avatar
    d_gosbell
    Icon for Super User rankSuper User
    5 years ago

    Try using the SELECTEDVALUE function instead of HASONEVALUE

    • o59393's avatar
      o59393
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        5 years ago

        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])))