topic Re: DATEDIFF with values in different Rows and Fields in DAX Commands and Tips

DATEDIFF with values in different Rows and Fields

DaniBi92 — Mon, 24 May 2021 19:20:14 GMT

Hi,

is it possible to use a DATEDIFF formulas with values in different Rows and Fields(or another DAX formulas)?

For example, i 've the following Data:

ITEM	STATUS	DATE1	DATE2
XXX	Receipt	01/05/2021
XXX	Delivery		05/05/2021
YYY	Receipt	04/04/2021
ZZZ	Receipt	30/04/2021
YYY	Delivery		07/04/2021
ZZZ	Delivery		11/05/2021

The result that i need is the sum of the days difference by Item. In my exemple the result must be 18:

ITEM	DATE1	DATE2	RESULTS
XXX	01/05/2021	05/05/2021	4
YYY	04/04/2021	07/04/2021	3
ZZZ	30/04/2021	11/05/2021	11
		RESULT:	18

Is there a DAX formulas to achive this result?

Thanks in advance.

Re: DATEDIFF with values in different Rows and Fields

Greg_Deckler — Mon, 24 May 2021 19:39:41 GMT

DaniBi92 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: DATEDIFF with values in different Rows and Fields

Fowmy — Mon, 24 May 2021 19:52:31 GMT

DaniBi92

You can create a DX table with the following code. Click New Table under the Modeling tab and paste the code given below, rename the table name as per yours.

Result Table = ADDCOLUMNS( ADDCOLUMNS( VALUES(Table1[ITEM]), "DATE1", CALCULATE(MIN(Table1[DATE1])), "DATE2", CALCULATE(MIN(Table1[DATE2])) ), "RESULT", DATEDIFF([DATE1],[DATE2],DAY) )

Re: DATEDIFF with values in different Rows and Fields

Jihwan_Kim — Tue, 25 May 2021 02:52:48 GMT

Hi, DaniBi92

Please check the below picture and the sample pbix file's link down below.

All measures are in the sample b pbix file, and all steps are numbered in front of each measure.

https://www.dropbox.com/s/4lwg972lhq9ki9q/danibi.pbix?dl=0

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM