topic Excel function to DAX in DAX Commands and Tips

Excel function to DAX

Anonymous — Wed, 04 May 2022 00:29:10 GMT

Hi,

I need some help converting an excel formula into DAX.

Excel: =DATE(YEAR(TODAY()),MONTH(TODAY())-[@[- Months]]+1,0)

- Months = is a selection with the month number values (1 thru 12)

Result should be: YYYY-DD

- Months Day

1 2022-04
2 2022-03
3 2022-02
4 2022-01

Any help will be appreciated. Been trying to convert this fucntion for a couple of days now.

Thanks.

Re: Excel function to DAX

amitchandak — Wed, 04 May 2022 01:20:41 GMT

Anonymous , You can create a measure

format(DATE(YEAR(TODAY()),MONTH(TODAY())-selectedvalues(month[month])+1,1), "YYYYMM")

format(DATE(YEAR(TODAY()),MONTH(TODAY())-selectedvalues(month[month])+1,1), "YYYYDD")

format(DATE(YEAR(TODAY()),MONTH(TODAY())-selectedvalues(month[month])+1,day(Today()) ), "YYYYMM")

format(DATE(YEAR(TODAY()),MONTH(TODAY())-selectedvalues(month[month])+1,day(Today()) ), "YYYYDD")

Re: Excel function to DAX

Jihwan_Kim — Wed, 04 May 2022 03:18:12 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new column.

Result CC = VAR _todaymonth = MONTH ( TODAY () ) VAR _todayyear = YEAR ( TODAY () ) VAR _condition = _todaymonth > 'Months Table'[@Months] RETURN IF ( _condition = TRUE (), ( _todayyear & "-" & FORMAT ( _todaymonth - 'Months Table'[@Months], "00" ) ) )

Re: Excel function to DAX

Anonymous — Wed, 04 May 2022 14:33:22 GMT

Thank you for your reply. I am going to give this a try.

Should I create measure columns for all excel functions in Power BI?

Thank you again.