topic Re: How to use Datediff with filtering conditions in DAX Commands and Tips

How to use Datediff with filtering conditions

PbiCeo — Mon, 25 May 2020 15:00:00 GMT

Hello everyone,

I would calculate Years using the following YearDate and Division.

YearDate	Division
2001/1/2	D1
2001/3/1	D1
2005/5/1	D2
2005/5/1	D2
2008/5/1	D2
2010/5/1	D2
2013/5/1	D3
2015/5/1	D3
2016/5/1	D3
2019/5/1	D3
2020/5/1	D4

For example, Years for the first D1 should be calculated like: year(2005/5/1 - 2001/1/2) 2005/5/1 is the first D2, next the first D2 - the second D1 and so on.

The expected result as below.

Years
4
4
8
8
5
3
7
5
4
1
0

Finally for D4, it shloud be like that: year(today() - 2020/5/1)
How can I do that?

I tried Datediff, but didn't work with filtering.
Any advice will be highly appreciated.

Thanks
Vadi,

Re: How to use Datediff with filtering conditions

Greg_Deckler — Mon, 25 May 2020 15:24:23 GMT

PbiCeo So you need to compare between rows. I would recommend using EARLIER as in this article. 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

In your case, probably something along the lines of:

Column = VAR __Next = MINX(FILTER('Table',[YearDate] > EARLIER([YearDate]) && [Division] <> EARLIER([Division]),[YearDate]) VAR __YearCurrent = YEAR([YearDate]) VAR __YearNext = IF(ISBLANK(__Next),YEAR(TODAY()),YEAR(__Next)) RETURN __YearNext - YearCurrent

Re: How to use Datediff with filtering conditions

PbiCeo — Wed, 27 May 2020 15:03:18 GMT

Hello Greg_Deckler,

Many thanks for your advice.

Is it possible to calculate like the below result?

It means, getting each first value of Division.


Years
4
4
8
8
8
8
7
7
7
7
0

Thanks,

Vadi

Re: How to use Datediff with filtering conditions

Greg_Deckler — Wed, 27 May 2020 16:05:43 GMT

Sure PbiCeo - It would be:

Column 2 = VAR __Min = MINX(FILTER('Table',[Division] = EARLIER([Division])),[YearDate]) VAR __Next = MINX(FILTER('Table',[YearDate] > EARLIER([YearDate]) && [Division] <> EARLIER([Division])),[YearDate]) RETURN IF(ISBLANK(__Next),0,YEAR(__Next) - YEAR(__Min))

I have attached a PBIX with both columns with syntax errors corrected.