Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jkaelin
Resolver I
Resolver I

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
dedelman_clng
Community Champion
Community Champion

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

View solution in original post

6 REPLIES 6
dedelman_clng
Community Champion
Community Champion

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

@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
Community Champion
Community Champion

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

@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.  

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.

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.