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
mscopuser
Below file has a solution which will work, I have create two columns and one measure.
If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!
Follow me on LinkedIn!!!
- mscopuser2 years agoFrequent Visitor
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.- Anonymous2 years agoNot applicable
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.