Forum Discussion
DAX for date difference
Hi, I have 2 dates "Start Date" and "End Date", I have to create a new column with the difference of these 2 dates as highlighled below. I want the new column to show up as 4/1/2021-4/30/2021...
Hi Anonymous ,
so here is a third variation:
Difference in Month = VAR _StartLessThanEnd = IF ( OR ( 'Table'[Start], 'Table'[End] ) = BLANK (), BLANK (), DATEDIFF ( 'Table'[Start], 'Table'[End], MONTH ) + 1 ) VAR _EndLessThanStart = IF ( OR ( 'Table'[Start], 'Table'[End] ) = BLANK (), BLANK (), DATEDIFF ( 'Table'[End], 'Table'[Start], MONTH ) + 1 ) RETURN if ( 'Table'[Start] < 'Table'[End], _StartlessThanEnd, _EndLessThanStart)With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
Hi FrankAT ,
Thak you for the prompt reply but the value is also wrong for these dates
start date =01-19-2022 -end date =03-31-2021 = 11 months but its giving -9
Hi Anonymous ,
so here is a third variation:
Difference in Month =
VAR _StartLessThanEnd =
IF (
OR ( 'Table'[Start], 'Table'[End] ) = BLANK (),
BLANK (),
DATEDIFF ( 'Table'[Start], 'Table'[End], MONTH ) + 1
)
VAR _EndLessThanStart =
IF (
OR ( 'Table'[Start], 'Table'[End] ) = BLANK (),
BLANK (),
DATEDIFF ( 'Table'[End], 'Table'[Start], MONTH ) + 1
)
RETURN
if ( 'Table'[Start] < 'Table'[End], _StartlessThanEnd, _EndLessThanStart)
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)