Forum Discussion
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...
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
__ResultCELL 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!!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.
