Forum Discussion

ssk_1984's avatar
ssk_1984
Icon for Helper II rankHelper II
3 years ago
Solved

Consecutive Row Count from date between Current Month and follow up 2 month

Hi Team,   Pleaes help me to create measure to get the below results   Vertical REPORT_DATE SLA_MISSES Current Month & Follow Two Months same value BFS 01-01-2022 Met   BFS 01-02-...
  • tamerj1's avatar
    3 years ago

    Hi ssk_1984 
    Please refer to attached sample file with the solution

    Current Month & Follow Two Months same value = 
    VAR CurrentDate = 'Table'[REPORT_DATE]
    VAR CurrentVerticalTable = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Vertical] ), 'Table'[REPORT_DATE] >= CurrentDate )
    VAR T1 = ADDCOLUMNS ( CurrentVerticalTable, "@Rank1", RANKX ( CurrentVerticalTable, [REPORT_DATE],, ASC, Dense ) )
    VAR T2 = FILTER ( T1, 'Table'[SLA_MISSES] = "Not Met" )
    VAR T3 = ADDCOLUMNS ( T2, "@Rank2", RANKX ( T2, [REPORT_DATE],, ASC, Dense ) )
    VAR T4 = FILTER ( T3, [@Rank1] = [@Rank2] )
    VAR Result = IF ( COUNTROWS ( T4 ) >= 3, 1 )
    RETURN
        Result