Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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=1 and 7/1/2021-9/30/2021=3
Solved! Go to Solution.
Hi @neelofarshama ,
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)
if the absolute number in 'Test Term' is correct and you want only to get reed of the negativ sign then use the following measure:
Difference in Month =
IF (
OR ( 'Table'[Start], 'Table'[End] ) = BLANK (),
BLANK (),
ABS( DATEDIFF ( 'Table'[Start], 'Table'[End], MONTH ) + 1 )
)
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 @neelofarshama ,
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)
adjust your formula like this:
= DATEDIFF('Table'[Start],'Table'[End],MONTH) + 1
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,
I cannot use +1 at the end blanks in start and end dtae columns and they would appear as 1 in my new column.
Hi @neelofarshama ,
use the following solution:
Difference in Month =
IF (
OR ( 'Table'[Start], 'Table'[End] ) = BLANK (),
BLANK (),
DATEDIFF ( 'Table'[Start], 'Table'[End], MONTH ) + 1
)
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 ,
This is working but there is an issue with dates whose end date(day) is greater than start date(day) its givning negative values as highlighted below
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |