Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MahamoodBi
Frequent Visitor

findout date difference between two dates and non dates

Hi PBI experts,

 

I have one simple requirment need to findout date differnece between ''Service date'' and ''Term.Date''

MahamoodBi_0-1663586993292.png

2.But for Emp.No - 19031 i dont have data in "Service Date" Column at that time i need to take data  from ''Valid from'' first column data (ex. - emp no - 19031 ,Valid from - 11-04-2019 and term date - 11-04-2022)

MahamoodBi_0-1663587499046.png

 

here i need to find date difference between Valid from and Term.Date) here i need find two dax function could you please suggest.

 

Thanks,

Shaik

 

6 REPLIES 6
Greg_Deckler
Super User
Super User

@MahamoodBi First part is easy, it is 

Measure =
  ( MAX([TermDate]) - MAX([ServiceDate]) ) * 1.

2nd part:

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler i need both dax function in single caluclated column ,could you please suggest

 

@MahamoodBi Right, that's a simple

IF( MAX('Table'[ServiceDate]) <> BLANK(),

  ( MAX([TermDate]) - MAX([ServiceDate]) ) * 1.,

  ... //second measure goes here

 )


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler i need to divided by 12,because i am finding date difference between two dates by month

MahamoodBi_0-1663591064923.png

 

second point coding i didný understood could you please cexplain

@MahamoodBi I can't provide you with a specific solution without sample data to build a model out and code it. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Emp.NoValid FromValid ToService.DateTermDate
1583301-09-2014 00:0003-11-2014 00:0001-04-201428-04-2022
04-11-2014 00:0008-01-2015 00:00
09-01-2015 00:0031-03-2015 00:00
1903111-04-2019 00:0031-10-2020 00:00 11-04-2022
01-11-2020 00:0006-07-2021 00:00
07-07-2021 00:0011-04-2022 00:00
1609706-10-2014 00:0003-11-2014 00:0006-10-201412-05-2022
04-11-2014 00:0011-06-2015 00:00
12-06-2015 00:0031-03-2016 00:00
1182121-01-2008 00:0024-06-2008 00:00 12-04-2022
25-06-2008 00:0030-09-2008 00:00
01-10-2008 00:0031-10-2008 00:00
01-11-2008 00:0031-12-2008 00:00

pls check the sample data 

1.i need to findout date difference between service date and term date.

2 if service date column is empty we should find date difference between Valid from(least value) and term date

3, above two condition should be single formula

 

thanks 

.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors