Max consecutive day count by month in Power BI table

[Viranga]

Hi,

I need to get the maximum consecutive day count according to the month. 

Ex: In June month, the max consecutive day count is 2.

     In August month, consecutive day counts are 2 and 3. The max consecutive day count is 3. (As in figure 03)

Figure 02:

Figure 03:

Any help would be appreciated.

Thanks in advance.

[Anonymous · Accepted solution]

Hi  @Viranga ,

Here are the steps you can follow：

1. In Power Query -- Add Column – Index Column – From 1.

2. Create calculated column.

Flag1 =
var _current=
MAXX(  FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])&&'Table'[Month]=EARLIER('Table'[Month])),[Date])
var _next=
MAXX(   FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])-1&&'Table'[Month]=EARLIER('Table'[Month])),[Date])
return
IF(
    _current-1 = _next,1,0)

Flag2 =
IF(
    [Flag1]=0,1,0)

Flag3 =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Index]<=EARLIER('Table'[Index])&&'Table'[Month]=EARLIER('Table'[Month])),[Flag2])

Count =
 COUNTX(
     FILTER(ALL('Table'),
     'Table'[Month]=EARLIER('Table'[Month])&&'Table'[Flag3]=EARLIER('Table'[Flag3])),[Date])

3. Create calculated table.

Table 2 =
SUMMARIZE(
    'Table','Table'[Month],
    "Maximum Consecutive days",
    MAXX(
        FILTER(ALL('Table'),'Table'[Month]=EARLIER('Table'[Month])),[Count]))

4. Result:

Best Regards,

Liu Yang

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