Forum Discussion
prashantg364
2 years agoHelper II
DAX formula
Cell Week abc 35 abc 34 abc 33 abc 32 abc 31 def 35 def 34 def 32 def 31 jkl 34 jkl 33 jkl 32 jkl 31 jkl 30 pqr 35 pqr 34 pqr 3...
Greg_Deckler
2 years agoCommunity Champion
prashantg364 Here is one way, PBIX attached below signature.
Measure 2 =
VAR __MinWeek = MINX( ALL('Table'[Week]), [Week] )
VAR __MaxWeek = MAXX( ALL('Table'[Week]), [Week] )
VAR __MaxWeekCell = MAX( 'Table'[Week] )
VAR __Count = COUNTROWS( DISTINCT( 'Table'[Week] ) )
VAR __Path = CONCATENATEX( DISTINCT( 'Table'[Week] ), [Week], "|", [Week], DESC )
VAR __Table =
ADDCOLUMNS(
GENERATESERIES( 1, __Count, 1 ),
"__Week", PATHITEM( __Path, [Value] ) + 0
)
VAR __Table2 =
ADDCOLUMNS(
__Table,
"__Gap",
VAR __CurWeek = [__Week]
VAR __Next = MAXX( FILTER( __Table, [__Week] < __CurWeek ), [__Week] )
VAR __Result = IF( __CurWeek = __MaxWeekCell, 1, __CurWeek - __Next )
RETURN
__Result
)
VAR __GapWeek = MAXX( FILTER( __Table2, [__Gap] > 1 ), [__Week] )
VAR __GapWeekFinal =
SWITCH( TRUE(),
__MaxWeekCell <> __MaxWeek, BLANK(),
__GapWeek = BLANK(), __MinWeek,
__GapWeek
)
VAR __Result = IF( __GapWeekFinal = BLANK(), BLANK(), CONCATENATEX( FILTER( DISTINCT( 'Table'[Week] ), [Week] >= __GapWeek ), [Week], "/", [Week], DESC ) )
RETURN
__Resultprashantg364
2 years agoHelper II
| CELL | Measure | Measure 2 | Output | output expected |
| ATG7130Z_7NB03_S04 | 33 | 33 | correct | |
| ATG8512Z_7NB03_S04 | 33/28 | 33/28 | not correct | 33 |
| ATG8524Z_7NB03_S02 | 33/26 | 33/26 | not correct | 33 |
| ATG8527Z_7NB03_S01 | 33 | 33 | correct | |
| ATGC004Z_7NB03_S03 | 33/28/27/26/25/24 | 33/28/27/26/25/24 | not correct | 33 |
| AYT0013H_7NB01_S02 | 33/32/31/28/27/24 | 33/32/31 | correct | |
| AYT0020H_7NB01_S01 | 33/32/31/30/29/28/27/26/25/24 | 33/32/31/30/29/28/27/26/25/24 | correct | |
Hi Greg_Deckler
Thanks for the help. Majority of the cases i am getting correct output, but still some cell is giving wrong output as shown in the table above.
Kindly re-look and help!!
- Greg_Deckler2 years agoCommunity Champion
prashantg364 You'll have to provide a more comprehensive set of sample data then so I can find the edge cases where things are going wrong.