Forum Discussion
Current Streak for Subject
- 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
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 member | Client | Projects | Sub-projects | Notes | Start date | Start time | End date | End time | Duration | Billed |
| Hans Anders | Learn Japanese | Listening | JapanesePod101 | 11.07.2024 | 21:31 | 11.07.2024 | 21:49 | 0:17:23 | No | |
| Hans Anders | Learn Japanese | Kanji | WaniKani | 12.07.2024 | 12:50 | 12.07.2024 | 13:44 | 0:54:13 | No | |
| Hans Anders | Learn Japanese | Vocab | jpdb | 12.07.2024 | 15:49 | 12.07.2024 | 16:24 | 0:35:16 | No | |
| Hans Anders | Learn Japanese | Kanji | WaniKani | 14.07.2024 | 20:08 | 14.07.2024 | 20:33 | 0:25:25 | No | |
| Hans Anders | Learn Japanese | Listening | JapanesePod101 | 15.07.2024 | 19:27 | 15.07.2024 | 19:40 | 0:12:58 | No | |
| Hans Anders | Learn Japanese | Kanji | WaniKani | 15.07.2024 | 20:20 | 15.07.2024 | 21:03 | 0:42:56 | No | |
| Hans Anders | Learn Japanese | Reading | Graded Readers | kaji study | 15.07.2024 | 21:43 | 15.07.2024 | 22:18 | 0:35:20 | No |
| Hans Anders | Learn Japanese | Listening | JapanesePod101 | 16.07.2024 | 07:48 | 16.07.2024 | 08:59 | 1:10:58 | No | |
| Hans Anders | Immersion | Comprehensible Japanese | 16.07.2024 | 12:01 | 16.07.2024 | 12:19 | 0:17:40 | No | ||
| Hans Anders | Learn Japanese | Kanji | WaniKani | 16.07.2024 | 20:10 | 16.07.2024 | 20:45 | 0:34:47 | No | |
| Hans Anders | Learn Japanese | Kanji | WaniKani | 16.07.2024 | 21:00 | 16.07.2024 | 21:01 | 0:00:46 | No | |
| Hans Anders | Learn Japanese | Listening | JapanesePod101 | 17.07.2024 | 08:14 | 17.07.2024 | 08:43 | 0:28:47 | No | |
| Hans Anders | Learn Japanese | Kanji | WaniKani | 17.07.2024 | 18:12 | 17.07.2024 | 18:32 | 0:19:33 | No | |
| Hans Anders | Learn Japanese | Vocab | Anki | 17.07.2024 | 19:15 | 17.07.2024 | 20:00 | 0:44:50 | No | |
| Hans Anders | Learn Japanese | Kanji | WaniKani | 17.07.2024 | 21:35 | 17.07.2024 | 21:48 | 0:12:42 | No |
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.
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.
- mscopuser2 years agoFrequent Visitor
The streak for how long I have been doing my subproject.
so let's say I have been studying WaniKani for on 16 th day and 17 th day my streak would be 2.
If forget to study on the 18 and I do study on day 19. My streak would be 1.
Things to note is that might be multiple same subprojects on day basis. It should count them as one study streak.
From that I want to have number (dax?) that I can use on visual card type. Keep in mind I want to have this per subject project. I am trying myself to find solutions that are provided already.