Forum Discussion

Jkaelin's avatar
Jkaelin
Icon for Resolver I rankResolver I
9 years ago
Solved

IF Function on Calculate Measure

Good morning,

 

I have a calculate measure that returns the product from a time period on a slicer.  It calculates the last 12 months return for an account.  When the dates on the slicer is less than 12 month time period, I want the measure to return a null, N/A, or Blank because there is not enough dates selected for a full 12 month return.  I was attempting this via IF statement, but it did not work in DAX.  

 

Current Formula Works when 12 or More Months Selected:

12 Month Return = CALCULATE(product(Table1[Return]]),DATESINPERIOD(Table1[Date],LASTDATE(Table1[Date]),-12,MONTH))

 

Failed Formula:

12 Month Return = IF(VALUES(

CALCULATE(product(Table1[Return]]),DATESINPERIOD(Table1[Date],LASTDATE(Table1[Date]),-12,MONTH))<12,BLANK(),

CALCULATE(product(Table1[Return]]),DATESINPERIOD(Table1[Date],LASTDATE(Table1[Date]),-12,MONTH))

 

Any advice or tips?  Kindly & Thank you,

James

Need Help
  • dedelman_clng's avatar
    dedelman_clng
    9 years ago

    It sounds like you want to test your IF statement based on the number of months, but your code is looking at the value of the calculation.

     

    VALUES requires a table/column reference.  Try the below:

     

    12 Month Return =
IF (
    DATEDIFF (
        FIRSTDATE ( ALLSELECTED ( Table1[Date] ) ),
            LASTDATE ( ALLSELECTED ( Table1[Date] ) ),
        MONTH
    )
        < 12,
    BLANK (),
    CALCULATE (
        PRODUCT ( Table1[Return] ),
        DATESINPERIOD ( Table1[Date], LASTDATE ( Table1[Date] ), -12, MONTH )
    )
)

     

    Hope this helps

    David

6 Replies

  • dedelman_clng's avatar
    dedelman_clng
    Icon for Community Champion rankCommunity Champion
    9 years ago

    It sounds like you want to test your IF statement based on the number of months, but your code is looking at the value of the calculation.

     

    VALUES requires a table/column reference.  Try the below:

     

    12 Month Return =
IF (
    DATEDIFF (
        FIRSTDATE ( ALLSELECTED ( Table1[Date] ) ),
            LASTDATE ( ALLSELECTED ( Table1[Date] ) ),
        MONTH
    )
        < 12,
    BLANK (),
    CALCULATE (
        PRODUCT ( Table1[Return] ),
        DATESINPERIOD ( Table1[Date], LASTDATE ( Table1[Date] ), -12, MONTH )
    )
)

     

    Hope this helps

    David

    • Jkaelin's avatar
      Jkaelin
      Icon for Resolver I rankResolver I
      9 years ago

      dedelman_clng

       

      Thanks for your response.  The formula you provided seems close.  The formula works if I hardcode dates in the following spot of: 

      FIRSTDATE ( ALLSELECTED ( Table1[Date] ) ),
              LASTDATE ( ALLSELECTED ( Table1[Date] ) ),

       

      However, the formula above reads every DATEDIFF interval in my date column.  I've tried many iterations but to not avail.  Should I have a different setup with a date column or calendar table?  Not sure how to best troubleshoot this.  

       

      Thank you, kindly,

      JK

      • Sean's avatar
        Sean
        Icon for Community Champion rankCommunity Champion
        9 years ago

        Jkaelin

        DATEDIFF Month is kind of "tricky" to use

        To see what I mean try it on 1/1/2016 to 12/31/2016 - you would expect to get 12 but you actually get 11

        Try this instead

        12 Month Return =
IF (
    DATEDIFF ( FIRSTDATE ( Table1[Date] ), LASTDATE ( Table1[Date] ), DAY ) < 365,
    BLANK (),
    CALCULATE (
        PRODUCT ( Table1[Return] ),
        DATESINPERIOD ( Table1[Date], LASTDATE ( Table1[Date] ), -12, MONTH )
    )
)