topic Re: findout date difference between two dates and non dates in DAX Commands and Tips

findout date difference between two dates and non dates

MahamoodBi — Mon, 19 Sep 2022 11:39:47 GMT

Hi PBI experts,

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

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)

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

Re: findout date difference between two dates and non dates

Greg_Deckler — Mon, 19 Sep 2022 11:57:10 GMT

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/339586.
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

Re: findout date difference between two dates and non dates

MahamoodBi — Mon, 19 Sep 2022 12:14:35 GMT

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

Re: findout date difference between two dates and non dates

Greg_Deckler — Mon, 19 Sep 2022 12:30:34 GMT

MahamoodBi Right, that's a simple

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

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

... //second measure goes here

)

Re: findout date difference between two dates and non dates

MahamoodBi — Mon, 19 Sep 2022 12:38:31 GMT

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

second point coding i didný understood could you please cexplain

Re: findout date difference between two dates and non dates

Greg_Deckler — Mon, 19 Sep 2022 12:40:37 GMT

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.

Re: findout date difference between two dates and non dates

MahamoodBi — Tue, 20 Sep 2022 03:33:01 GMT

Emp.No	Valid From	Valid To	Service.Date	TermDate
15833	01-09-2014 00:00	03-11-2014 00:00	01-04-2014	28-04-2022
04-11-2014 00:00	08-01-2015 00:00
09-01-2015 00:00	31-03-2015 00:00
19031	11-04-2019 00:00	31-10-2020 00:00		11-04-2022
01-11-2020 00:00	06-07-2021 00:00
07-07-2021 00:00	11-04-2022 00:00
16097	06-10-2014 00:00	03-11-2014 00:00	06-10-2014	12-05-2022
04-11-2014 00:00	11-06-2015 00:00
12-06-2015 00:00	31-03-2016 00:00
11821	21-01-2008 00:00	24-06-2008 00:00		12-04-2022
25-06-2008 00:00	30-09-2008 00:00
01-10-2008 00:00	31-10-2008 00:00
01-11-2008 00:00	31-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