cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jkaelin Member
Member

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

1 ACCEPTED SOLUTION

Accepted Solutions
dedelman_clng New Contributor
New Contributor

Re: IF Function on Calculate Measure

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 6
dedelman_clng New Contributor
New Contributor

Re: IF Function on Calculate Measure

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 Member
Member

Re: IF Function on Calculate Measure

@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

dedelman_clng New Contributor
New Contributor

Re: IF Function on Calculate Measure

Creating a separate calender table, creating a relationship between the two and then using Date from the calendar table (in the measure) is best practice (also, use the calendar table as the source for the slicer).  That should allow the filters to propogate correctly.

Jkaelin Member
Member

Re: IF Function on Calculate Measure

@dedelman_clng

 

Thanks for the quick response.  I was able to get it to work, thanks to you.  I used an alternative method but it worked as well.  I used MIN/MAX within DATEDIFF.

DATEDIFF (
MIN(Table1[Date] ),
MAX( Table1[Date]) ,
MONTH
)

 

Thanks again,

JK

Super User
Super User

Re: IF Function on Calculate Measure

@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

Datediff Month.png

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 )
    )
)
Highlighted
Jkaelin Member
Member

Re: IF Function on Calculate Measure

@Sean

I notied the discrepency within DATEIFF regarding 11 vs 12 months for one year.  I adjusted my formula accordingly, but it's still confusing.  I'll study it within the column context you visualized below.  Thank you for your help.