Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
@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
@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
)
@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
@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.
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
.
User | Count |
---|---|
40 | |
26 | |
22 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |