Forum Discussion

mscopuser's avatar
mscopuser
Frequent Visitor
2 years ago
Solved

Current Streak for Subject

Hi,   I have this table (see below). From that table I want to have calculation of current streak for doing a sub project. I have tried chat gpt but no luck.   Team member Client Projects...
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    mscopuser Sure, it's almost identical. Attached PBIX file below sig has the Current Streak measure as well as an example of Longest Streak for Project instead of sub-project.

    Current Streak Sub-Project = 
        VAR __SubProject = MAX( 'Table'[Sub-projects] )
        VAR __MaxDate = MAXX( ALL( 'Table' ), [Start date] )
        VAR __MinDate = 
            MINX( 
                FILTER( 
                    ALL( 'Table' ), 
                    [Sub-projects] = __SubProject 
                ), 
                [Start date] 
            )
        VAR __Table = 
            ADDCOLUMNS(
                CALENDAR( __MinDate, __MaxDate ),
                "__Count", 
                    VAR __Date = [Date]
                    VAR __Result = 
                        IF( 
                            COUNTROWS( 
                                FILTER( 
                                    ALL( 'Table' ), 
                                    [Start date] = __Date && [Sub-projects] = __SubProject 
                                ) 
                            ), 1, 0 
                        )
                RETURN
                    __Result
            )
        VAR __Table1 = 
            ADDCOLUMNS(
                __Table,
                "Cthulhu", 
                    VAR __Index = [Date]
                    VAR __Group = [__Count]
                    VAR __TmpTable1 = FILTER( __Table, [__Count] = __Group && [Date] < __Index)
                    VAR __TmpTable2 = 
                        ADDCOLUMNS( 
                            __TmpTable1, 
                            "__diff", 
                            ( 
                                [Date] - 
                                MAXX( 
                                    FILTER( 
                                        __Table, 
                                        [Date] < EARLIER( [Date] ) && [__Count] = EARLIER( [__Count] ) 
                                    ), 
                                    [Date] 
                                ) 
                            ) * 1. 
                        )
                    VAR __Max = MAXX( __TmpTable2, [Date])
                    VAR __MaxStart = MAXX( FILTER(__TmpTable2, [__diff] > 1), [Date])
                    VAR __TmpTable3 = FILTER( __TmpTable2, [Date] >= __MaxStart)
                    VAR __Result = 
                        SWITCH( TRUE(),
                            ISBLANK( __Max ), 1, 
                            __Max = __index - 1, COUNTROWS( __TmpTable3 ) + 1, 
                            1
                        )
                RETURN
                    __Result
            )
        VAR __CurrentCount = MAXX( FILTER( __Table1, [Date] = __MaxDate ), [__Count] )
        VAR __Result = IF( __CurrentCount = 0, 0, MAXX( FILTER( __Table1, [Date] = __MaxDate ), [Cthulhu] ) )
    RETURN
        __Result