How to use Datediff with filtering conditions

[PbiCeo]

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,

[Greg_Deckler]

@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/3395...

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

[PbiCeo]

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

[Greg_Deckler · Accepted solution]

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.