Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
PbiCeo
Helper II
Helper II

How to use Datediff with filtering conditions

Hello everyone,


I would calculate Years using the following YearDate and Division.

 

 

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

 

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,

1 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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Community Champion
Community Champion

@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

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

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

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.