Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

If function + left and right

Hi all, 

I have the below table and trying to make an end of month formula, but I don't the formula calculates wrong. 

EOM = EOMONTH(IF(AND('Finance Forecast - Test'[FYPeriod]>7, 'Finance Forecast - Test'[FYPeriod]<12) , DATE(RIGHT('Finance Forecast - Test'[FY],4), 'Finance Forecast - Test'[FYPeriod]+6, 1), DATE(2000+LEFT('Finance Forecast - Test'[FY],2), 'Finance Forecast - Test'[FYPeriod]-6, 1)),0)
 
Can any tell me please why the formula doesn't calculate correctly? 
 
Thanks 
Help with DAX
if
if statement
  • Anonymous's avatar
    Anonymous
    5 years ago

    That is the answer of my question for anyone interested in 

    EOM = EOMONTH(IF('Finance Forecast - Test'[FYPeriod]<=6, DATE(LEFT('Finance Forecast - Test'[FY],4), 'Finance Forecast - Test'[FYPeriod]+6, 1), DATE(2000+RIGHT('Finance Forecast - Test'[FY],2), 'Finance Forecast - Test'[FYPeriod]-6, 1)),0)

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try like

    EOM =
    var _col = if([FYPeriod] >=6 , [FYPeriod] -6, [FYPeriod] +6)
    return
    eomonth(date(left([FY],4),_col,1),0)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak I don't know why my reply wasn't shown up here

      the formula is incorrect again 

      i cannot calculate years  

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Anonymous, do it in Power Query. It'll be sooooo much simpler. In PQ, split the FY column and get the first component, then create a string from Month and the new column adding "1" to obtain something like "June 1, 2019", then turn this into a real date using PQ functions or the functionality in the ribbon and then use the datetime intelligence in PQ (not in DAX!), to get the last day of the month. It's much easier done than said!... which is the best part of doing such things in PQ. And it'll be 100% error-free.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    That is the answer of my question for anyone interested in 

    EOM = EOMONTH(IF('Finance Forecast - Test'[FYPeriod]<=6, DATE(LEFT('Finance Forecast - Test'[FY],4), 'Finance Forecast - Test'[FYPeriod]+6, 1), DATE(2000+RIGHT('Finance Forecast - Test'[FY],2), 'Finance Forecast - Test'[FYPeriod]-6, 1)),0)