topic Re: If function + left and right in DAX Commands and Tips

If function + left and right

Anonymous — Wed, 23 Sep 2020 05:57:07 GMT

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

Re: If function + left and right

amitchandak — Wed, 23 Sep 2020 06:36:31 GMT

Anonymous , Try like

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

Re: If function + left and right

Anonymous — Wed, 23 Sep 2020 07:33:40 GMT

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.

Re: If function + left and right

Anonymous — Wed, 23 Sep 2020 23:30:29 GMT

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

the formula is incorrect again

i cannot calculate years

Re: If function + left and right

Anonymous — Thu, 24 Sep 2020 00:15:24 GMT

Anonymous I am not good in Query, can you help me, please?

Re: If function + left and right

Anonymous — Thu, 24 Sep 2020 08:27:51 GMT

https://youtu.be/ioVM77JDIXE

Re: If function + left and right

Anonymous — Thu, 24 Sep 2020 23:14:50 GMT

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)