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 memberClientProjectsSub-projectsNotesStart dateStart timeEnd dateEnd timeDurationBilled
Hans AndersLearn JapaneseListeningJapanesePod101 17.07.202408:1417.07.202408:430:28:47No
Hans AndersLearn JapaneseListeningJapanesePod101 16.07.202407:4816.07.202408:591:10:58No
Hans AndersLearn JapaneseListeningJapanesePod101 15.07.202419:2715.07.202419:400:12:58No
Hans AndersLearn JapaneseListeningJapanesePod101 11.07.202421:3111.07.202421:490:17:23No
Hans AndersLearn JapaneseListeningJapanesePod101 07.07.202421:5607.07.202422:290:32:31No
Hans AndersLearn JapaneseListeningJapanesePod101 07.07.202413:1207.07.202413:360:24:12No
Hans AndersLearn JapaneseListeningJapanesePod101 06.07.202419:4206.07.202420:130:30:31No
Hans AndersLearn JapaneseListeningJapanesePod101 02.07.202407:3302.07.202408:240:51:21No

 

If I skip a day I should sart at 0.

 

An bonus would be to choose on project and Client basis. But I rekon the code would be easy to modify.

The expected output is number that I can use for my card visual. 



Need Help
  • 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

11 Replies

    • mscopuser's avatar
      mscopuser
      Frequent Visitor
      2 years ago

      Updated. I just want to have number that I can use on a card visual. So I can see my current streak in studying serveral sub projects. 

    • mscopuser's avatar
      mscopuser
      Frequent Visitor
      2 years ago

      Firstly thank you for your help so far. I should have provided a better opening post.

      My provided data sample was skewd, sorry for that see a different one below. Same style but more input.

      Team memberClientProjectsSub-projectsNotesStart dateStart timeEnd dateEnd timeDurationBilled
      Hans AndersLearn JapaneseListeningJapanesePod101 11.07.202421:3111.07.202421:490:17:23No
      Hans AndersLearn JapaneseKanjiWaniKani 12.07.202412:5012.07.202413:440:54:13No
      Hans AndersLearn JapaneseVocab jpdb 12.07.202415:4912.07.202416:240:35:16No
      Hans AndersLearn JapaneseKanjiWaniKani 14.07.202420:0814.07.202420:330:25:25No
      Hans AndersLearn JapaneseListeningJapanesePod101 15.07.202419:2715.07.202419:400:12:58No
      Hans AndersLearn JapaneseKanjiWaniKani 15.07.202420:2015.07.202421:030:42:56No
      Hans AndersLearn JapaneseReadingGraded Readerskaji study15.07.202421:4315.07.202422:180:35:20No
      Hans AndersLearn JapaneseListeningJapanesePod101 16.07.202407:4816.07.202408:591:10:58No
      Hans AndersImmersionComprehensible Japanese  16.07.202412:0116.07.202412:190:17:40No
      Hans AndersLearn JapaneseKanjiWaniKani 16.07.202420:1016.07.202420:450:34:47No
      Hans AndersLearn JapaneseKanjiWaniKani 16.07.202421:0016.07.202421:010:00:46No
      Hans AndersLearn JapaneseListeningJapanesePod101 17.07.202408:1417.07.202408:430:28:47No
      Hans AndersLearn JapaneseKanjiWaniKani 17.07.202418:1217.07.202418:320:19:33No
      Hans AndersLearn JapaneseVocabAnki 17.07.202419:1517.07.202420:000:44:50No
      Hans AndersLearn JapaneseKanjiWaniKani 17.07.202421:3517.07.202421:480:12:42No

       

      As far I can tell your current solution does not provide a parameter to define wich Sub-projects to count. I might be wrong. If so please correct me. 

      As far for Sub-projects I want to be able to count how long my streak is for:
      Sub-projects:
      JapanesePod101
      WaniKani
      Graded Readers
      Anki

      There are more sub-projects in the complete file or maybe added in the feature. 
      I don't mind having different dax calculations or addtional coloums added. 

      The format won't be different in the feature as it is export from a tool I dont have control over and I don't see them adding more datapionts. 

      -----

      Additional req is a steakcounter that can go by Projects level. So for each project.

      Hopefully I have been complete this time in my request.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi, mscopuser 

         

        Based on the data you provided, give an example of what you would expect the output to be?

         

        Best Regards,

        Community Support Team _Charlotte

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    mscopuser Found this from the email you sent. I believe I solved this, see the attached PBIX below signature. 

    Longest Streak = 
    VAR __SubProject = MAX( 'Table'[Sub-projects] )
    VAR __MinDate = MINX( FILTER( ALL( 'Table' ), [Sub-projects] = __SubProject ), [Start date] )
    VAR __MaxDate = MAXX( 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 __Result = MAXX( FILTER( __Table1, [__Count] <> 0 ), [Cthulhu] )
RETURN
    __Result
    • mscopuser's avatar
      mscopuser
      Frequent Visitor
      2 years ago

      You are life saver, I am so happy now. Chatgpt could not help me after spending 2 hours today.

       

      It's almost perfect, is see you have calculated the longest streak wich is data point I totally can use for me power bi dashboard I am trying to build.

       

      Could also you also provide one for the current streak? 

       

      I would be agian thank full 

       

      EDIT: I mean the current streak of each subproject. So if I would start to day it would be 1. If started a week ago it would be 7.

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        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