Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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.
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |