Dax Soustract DATE

[FZOU]

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 of monthsf for exp :

MonthN

1 

10 

15 

N..

The demanded task is to have the difference between the two columns for exp : 

"2019-10" - 9 = "2019-01"

Anyone have an idea how to do it please !

[amitchandak]

@FZOU 

Try like

format(date(year(left([month-year]),4),month(right([month-year]),2)-9,1),"YYYY-MM")

Here -9 is the number you want to subtract

[FZOU]

@amitchandak  it's impssible to format text as date !

[v-xuding-msft · Accepted solution]

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

 

[FZOU]

@v-xuding-msftThank you for your help,it's work perfectly 🙂

[amitchandak]

Try like

format(date(year(left([month-year]),4),month(mid([month-year],search("-",[month-year],1,0)+1,len([month-year])))-9,1),"YYYY-MM")

To get the correct value of the month please check this separately - mid([month-year],search("-",[month-year],1,0)+1,len([month-year]))

[Greg_Deckler]

Perhaps:

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

[FZOU]

@Greg_Decklerthank you for your response but i had this result 

[Greg_Deckler]

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]

@Greg_Deckleri keep get the same wrong result