Forum Discussion

FZOU's avatar
FZOU
Icon for Helper IV rankHelper IV
6 years ago
Solved

Dax Soustract DATE

Hello, I need your help guys to achieve a formula.   I have a column that containt "Year-Month" for exp : YearMonth 2019-01 2019-02 2019-03 N..   I have another column that contains number ...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi FZOU ,

    Can you change the data type of column "YearMonth" from Text to Date? If yes, maybe you could try this:

    Column =
DATE ( YEAR ( 'Table'[YearMonth] ), MONTH ( 'Table'[YearMonth] ) - 'Table'[MonthN], DAY ( 'Table'[YearMonth] ) )

    And then format the new column as "yyyy-mm".

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Perhaps:

 

Column =
  VAR __YearMonth = VALUE(SUBSTITUTE([YearMonth],"-",""))
  VAR __NewYearMonth = (__YearMonth - [MonthN]) & ""
RETURN
  LEFT(__NewYearMonth,4) & "-" & RIGHT(__NewYearMonth,2)
  • FZOU's avatar
    FZOU
    Icon for Helper IV rankHelper IV
    6 years ago

    Greg_Decklerthank you for your response but i had this result 

     

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Right, forgot the padded zero, try this one:

       

      Column =
  VAR __YearMonth = VALUE(SUBSTITUTE([YearMonth],"-",""))
  VAR __NewYearMonth = (__YearMonth - [MonthN]) & ""
RETURN
  LEFT(__NewYearMonth,4) & "-" & FORMAT(RIGHT(__NewYearMonth,2),"0#")

       

      Might be ## or 0#, one of them or both give a padded right-zero. 

      • FZOU's avatar
        FZOU
        Icon for Helper IV rankHelper IV
        6 years ago

        Greg_Deckleri keep get the same wrong result