Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ncraft
Frequent Visitor

call of cuthuluh (cumulative consecutive values with conditions)

on another thread ( "Gaps&Islands" consecutive days in student absences ) , I got help from @Greg_Deckler when he supplied me with a link to his forumula approriatly named "cuthulhu". 

Here is the full formula used as a solution in a calculated column: 

consecutive_absences =
VAR __index = CALCULATE(MAX('attendance_data'[date])) 
VAR __group = CALCULATE(MAX('attendance_data'[student_id]))
VAR __tmpTable1 = FILTER(ALL('attendance_data'),'attendance_data'[student_id]=__group && 'attendance_data'[date]<__index
VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",'attendance_data'[date] - MAXX(FILTER(ALL('attendance_data'),'attendance_data'[date]<EARLIER('attendance_data'[date]) && 'attendance_data'[student_id]=EARLIER('attendance_data'[student_id])),'attendance_data'[date])) 
VAR __max = MAXX(__tmpTable2,'attendance_data'[date]
VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),'attendance_data'[date]
VAR __tmpTable3 = FILTER(__tmpTable2,'attendance_data'[date]>=__maxStart
VAR cuthulhu = IF(ISBLANK(__max),1,IF(__max=__index-1,COUNTROWS(__tmpTable3)+1,1))
RETURN
cuthulhu


Now, business has added another layer to the problem. I have an additional column named [attendance_value] that we want to filter by. [attendance_value] is always going to equal 1.0 or 0.5, and they dont want 0.5 to factor in as consecutive. I tried adding a simple filter in two ways but was not successful. 

VAR __index = CALCULATE(MAX('attendance_data'[date]), 'attendance_data'[attendance_value]=1

or

VAR __tmpTable1 = FILTER(ALL('attendance_data'),'attendance_data'[student_id]=__group && 'attendance_data'[date]<__index && 'attendance_data'[attendance_value]=1

These changes did not yeild the right result. 

Here is sample data where I manually created a column called [desired_cuthulhu] and entered each cell value to equal the result I am trying to get from [cuthuhlu]

 

student_iddateattendance_valuecuthuluhdesired_cuthulhu
2691822/1/2021111
2691822/2/2021122
2691822/3/2021133
2691822/4/2021144
2691822/5/2021155
2691822/8/20210.511
2691822/9/2021121
2691822/10/2021132
2691822/11/2021143
2691822/12/2021154
2691822/16/20210.511
2691822/17/2021121
2691822/18/20210.531
2691822/19/2021141
2691822/20/2021152
1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

Hi, @ncraft 

Here is my solution.

Remove the statement at the comment you can get unconditional consecutive counts


Try to create a measure like this:

 

_cuthuluh_WithConditions = 
VAR _FirstToLast_Table =
    FILTER(
        ALL( 'Table' ),
        'Table'[student_id] = MAX( 'Table'[student_id] )
            && 'Table'[date] <= MAX( 'Table'[date] )
            &&'Table'[attendance_value]=1//Conditions:value=1
    )
VAR _Last =
    CALCULATE( MAX( 'Table'[date] ), _FirstToLast_Table )
VAR _First =
    CALCULATE( MIN( 'Table'[date] ), _FirstToLast_Table )
VAR _AllValue_List =
    GENERATESERIES( _First, _Last, 1 )
VAR _currentList =
    SUMMARIZE( _FirstToLast_Table, [date] )
VAR _MisssList =
    EXCEPT( _AllValue_List, _currentList )
VAR _countRows =
    COUNTROWS( _MisssList )
VAR _MaxOfMisssList =
    IF(
        _countRows = 0,
        _First - 1,
        MAXX( EXCEPT( _AllValue_List, _currentList ), [Value] )
    )
VAR _diff =IF(
    MAX('Table'[attendance_value])=1,//Conditions:value=1
    DATEDIFF( _MaxOfMisssList, _Last, DAY ),1)
RETURN
    _diff

 

Result:

vangzhengmsft_0-1643092005807.png

Please refer to the attachment below for details.

Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng

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

View solution in original post

3 REPLIES 3
v-angzheng-msft
Community Support
Community Support

Hi, @ncraft 

Here is my solution.

Remove the statement at the comment you can get unconditional consecutive counts


Try to create a measure like this:

 

_cuthuluh_WithConditions = 
VAR _FirstToLast_Table =
    FILTER(
        ALL( 'Table' ),
        'Table'[student_id] = MAX( 'Table'[student_id] )
            && 'Table'[date] <= MAX( 'Table'[date] )
            &&'Table'[attendance_value]=1//Conditions:value=1
    )
VAR _Last =
    CALCULATE( MAX( 'Table'[date] ), _FirstToLast_Table )
VAR _First =
    CALCULATE( MIN( 'Table'[date] ), _FirstToLast_Table )
VAR _AllValue_List =
    GENERATESERIES( _First, _Last, 1 )
VAR _currentList =
    SUMMARIZE( _FirstToLast_Table, [date] )
VAR _MisssList =
    EXCEPT( _AllValue_List, _currentList )
VAR _countRows =
    COUNTROWS( _MisssList )
VAR _MaxOfMisssList =
    IF(
        _countRows = 0,
        _First - 1,
        MAXX( EXCEPT( _AllValue_List, _currentList ), [Value] )
    )
VAR _diff =IF(
    MAX('Table'[attendance_value])=1,//Conditions:value=1
    DATEDIFF( _MaxOfMisssList, _Last, DAY ),1)
RETURN
    _diff

 

Result:

vangzhengmsft_0-1643092005807.png

Please refer to the attachment below for details.

Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng

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

Greg_Deckler
Community Champion
Community Champion

@ncraft I *think* the only change you need to your formula is:

VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",'attendance_data'[date] - MAXX(FILTER(ALL('attendance_data'),'attendance_data'[date]<EARLIER('attendance_data'[date]) && 'attendance_data'[student_id]=EARLIER('attendance_data'[student_id]) && 'attendance_data'[attendance_value]=1),'attendance_data'[date])) 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler No dice, 
Making the change almost worked, but in many cases ist still doesnt, and I cant understand why. Consider the sample data below. After applying the change you suggested, 0.5 values are still counted

student_iddateattendance_valuecuthulhu
29974011/5/202011
29974011/6/202012
29974011/13/20200.51
29974012/2/202011
2997403/9/202112
2997403/10/202113
2997403/11/202114
2997403/12/20210.55
2997403/26/202111
2997403/30/202111
2997403/31/20210.52
2997404/1/202113
2997404/2/20210.52
2997404/5/202111
2997404/6/20210.52
2997404/7/20210.53
2997404/8/202112
2997404/12/202111
2997404/13/20210.52
2997404/14/202113
2997404/15/20210.52
2997404/19/20210.51
2997404/20/202112
2997404/21/20210.52
2997404/22/20210.53
2997404/23/202112
2997405/7/20210.51
2997405/10/202111
2997405/11/202112


I am not sure what the solution is, but each of these attendance entries also has its own unique ID, I think what I will do as a workaround for my problem is to use Power M quary to make a new table referecing the origional table, filter out the 0.5 values, apply the cuthulhu formula, and then perform a merge quaries to put cuthuluh back on my origional table. 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.