Forum Discussion
Helper IVDax 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 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 !
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".
9 Replies
- Greg_Deckler
Community Champion
Perhaps:
Column = VAR __YearMonth = VALUE(SUBSTITUTE([YearMonth],"-","")) VAR __NewYearMonth = (__YearMonth - [MonthN]) & "" RETURN LEFT(__NewYearMonth,4) & "-" & RIGHT(__NewYearMonth,2)
FZOU- 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.
- amitchandak
Super User
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 !
- 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]))
