cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
neelofarshama
Post Partisan
Post Partisan

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.

neelofarshama_0-1620326591394.png

I want the new column to show up as 4/1/2021-4/30/2021=1 and 7/1/2021-9/30/2021=3  

I am using DAX DATEDIFF(Opportunity[Start Date].[Date],Opportunity[End Date].[Date],MONTH) for this but its not including the current start month for 4/1/2021-4/30/2021=0 but I want it to show up as 1.
 
Please help.
 
 
1 ACCEPTED SOLUTION

Hi @neelofarshama ,

so here is a third variation:

 

07-05-_2021_17-00-01.png

 

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)

 

View solution in original post

7 REPLIES 7
FrankAT
Super User II
Super User II

Hi @neelofarshama 

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

neelofarshama_0-1620385284220.png

 

Hi @neelofarshama ,

so here is a third variation:

 

07-05-_2021_17-00-01.png

 

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)

 

View solution in original post

FrankAT
Super User II
Super User II

Hi @neelofarshama 

adjust your formula like this:

 

06-05-_2021_21-17-22.png

 

= 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:

 

06-05-_2021_22-05-59.png

 

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

neelofarshama_0-1620384092615.png

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors