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

        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!