Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Excel function to DAX

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. 

DAX
function

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , You can create a measure

     

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

     

     

    or

     

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

     

    or

     

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

     

     

    or

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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. 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

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