cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Count consecutive days based on selected date by location

Hi, Please help!!

 

Left side in the below image is the data set. I need to count the number of consecutive occurences of digit 1.

Count has to happen based on the selected date in the report. Like if select 01-May-2020, then consec count = 3

poweringnaag_0-1599409097487.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Count consecutive days based on selected date by location

Hi, @poweringnaag 

 

Based on your description, I created data to reproduce your scenraio. The pbix file is attached in the end.

Table:

a1.png

 

You may create a measure as below.

Result = 
var tab = 
ADDCOLUMNS(
    ALL('Table'),
    "L1",
    CALCULATE(
        MAX('Table'[Gen. Date]),
        FILTER(
            ALL('Table'),
            'Table'[Loc. No.]=EARLIER('Table'[Loc. No.])&&
            'Table'[Gen. Date]<EARLIER('Table'[Gen. Date])&&
            'Table'[S+U=24]=1
        )
    ),
    "L",
    CALCULATE(
        MAX('Table'[Gen. Date]),
        FILTER(
            ALL('Table'),
            'Table'[Loc. No.]=EARLIER('Table'[Loc. No.])&&
            'Table'[Gen. Date]<EARLIER('Table'[Gen. Date])
        )
    )
)
var newtab = 
ADDCOLUMNS(
    tab,
    "flag",
    IF(
       [L1]=[L]&&NOT(ISBLANK([L])),
     1,0
    )
)
var t = 
ADDCOLUMNS(
    newtab,
    "Re",
    var _loc = [Loc. No.]
    var _date = [Gen. Date]
    return
    IF(
        [S+U=24]=1,
        IF(
            ISBLANK([L1])||[flag]=0,
            1,
            1+
            COUNTROWS(
                FILTER(
                    newtab,
                    [Loc. No.]=_loc&&
                    [Gen. Date]<=_date&&
                    [flag]=1&&
                    NOT(ISBLANK([L]))
                )
            )
        )
    )
)
var _result = 
SUMX(
    SUMMARIZE(
        'Table',
        'Table'[Loc. No.],
        "x",
        SUMX(
            FILTER(
                t,
                [Gen. Date]=SELECTEDVALUE('Table'[Gen. Date])&&
                [Loc. No.]=EARLIER('Table'[Loc. No.])
            ),
            [Re]
        )
    ),
    [x]
)
return
IF(
    ISBLANK(_result),
    0,
    _result
)

 

Result:

a2.png

 

a3.png

 

 

Best Regards

Allan

 

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

7 REPLIES 7
Highlighted
Super User IX
Super User IX

Re: Count consecutive days based on selected date by location

@poweringnaag , Create a new column like this and add that

 

if(not(isblank(maxx(filter(Table,[Gen Date] = earlier([Gen Date])-1),[Gen Date]))),1,0)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IX
Super User IX

Re: Count consecutive days based on selected date by location

@poweringnaag - OK, if this has to be dynamic then it has to be a measure, not a column. So, perhaps something like this:

Measure =
  VAR __Table =
    ADDCOLUMNS(
      ADDCOLUMNS(
        'Table',
        "Next",MINX(FILTER(ALL('Table'),[Gen Date]>EARLIER([Gen Date])),[Gen Date])
      ),
      "Diff",([Next] - [Gen Date])*1.
    )
RETURN
  COUNTROWS(FILTER(__Table,[Diff]=1))

Wasn't able to test. If you provide data in text in a table that I can copy, will mock it up in a PBIX for you.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Count consecutive days based on selected date by location

@Greg_Deckler  - Thank you helping me on this. Please find below the sample data. a pbix file will help.

 

Gen. DateLoc. No.GFFMSUS+U=24
01-Apr-201230.005.800.000.500
02-Apr-201230.0012.800.001.000
29-Apr-201233.700.0012.0012.001
30-Apr-201234.700.0012.0012.001
01-May-201230.000.0012.0012.001
02-May-201230.000.000.000.000
07-May-201230.000.000.000.000
08-May-201230.000.0011.0013.001
30-May-201230.000.000.000.000
31-May-201230.800.000.000.000
01-Jun-201230.000.000.000.000
02-Jun-201230.000.000.000.000
03-Jun-201230.000.000.000.000
04-Jun-201230.000.000.000.000
08-Jun-201230.000.000.000.000
12-Jun-201230.000.000.000.000
30-Jun-201233.600.008.0016.001
01-Apr-202124.400.000.000.000
02-Apr-202121.400.000.000.000
29-Apr-202120.200.000.000.000
30-Apr-202120.000.000.000.000
01-May-202121.501.000.000.600
02-May-202120.000.000.000.000
07-May-202121.000.000.000.000
08-May-202120.200.100.000.000
30-May-2021212.0012.000.000.000
31-May-2021212.0012.000.000.400
01-Jun-202128.0016.000.000.000
02-Jun-202125.0019.000.000.000
03-Jun-202120.000.000.000.000
04-Jun-202120.000.000.000.000
05-Jun-2021212.0012.000.000.000
08-Jun-2021212.0012.000.000.000
12-Jun-202120.000.000.000.000
30-Jun-2021211.0013.0010.0014.001

 

I applied both suggestions from Amit and Greg and below is the result.

 
 

Capture.JPG

Highlighted
Helper I
Helper I

Re: Count consecutive days based on selected date by location

Thank you @amitchandak . I created a column with your suggested defintion and included in the below post. Request you to review and let me know. Should I change to get the correct result?

Highlighted
Super User IX
Super User IX

Re: Count consecutive days based on selected date by location

@poweringnaag - I have included a PBIX file below sig. You want Table (36). Not sure if it is what you want. Are you trying to emulate S+U=24 column is that the desired output? Please confirm or deny. If no, then what is the desired output?

Column = 
  VAR __NextDate = MINX(FILTER(ALL('Table (36)'),[Gen. Date]>EARLIER([Gen. Date]) && [Loc. No.]=EARLIER([Loc. No.]) && [S+U=24]=1),[Gen. Date])
  VAR __Next = MAXX(FILTER('Table (36)',[Gen. Date]=__NextDate),[S+U=24])
RETURN
  IF([S+U=24]=1 && __Next=1,1,0)

  


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Count consecutive days based on selected date by location

@Greg_Deckler  - Below is the output I am trying to get..For a date selected I want to see the consecutive count that represents the outage. Its like if Scheduled (S) + Unscheduled (U) = 24 hrs in a day, then it means there is a outage on the machine/location.

output.JPG

Highlighted
Community Support
Community Support

Re: Count consecutive days based on selected date by location

Hi, @poweringnaag 

 

Based on your description, I created data to reproduce your scenraio. The pbix file is attached in the end.

Table:

a1.png

 

You may create a measure as below.

Result = 
var tab = 
ADDCOLUMNS(
    ALL('Table'),
    "L1",
    CALCULATE(
        MAX('Table'[Gen. Date]),
        FILTER(
            ALL('Table'),
            'Table'[Loc. No.]=EARLIER('Table'[Loc. No.])&&
            'Table'[Gen. Date]<EARLIER('Table'[Gen. Date])&&
            'Table'[S+U=24]=1
        )
    ),
    "L",
    CALCULATE(
        MAX('Table'[Gen. Date]),
        FILTER(
            ALL('Table'),
            'Table'[Loc. No.]=EARLIER('Table'[Loc. No.])&&
            'Table'[Gen. Date]<EARLIER('Table'[Gen. Date])
        )
    )
)
var newtab = 
ADDCOLUMNS(
    tab,
    "flag",
    IF(
       [L1]=[L]&&NOT(ISBLANK([L])),
     1,0
    )
)
var t = 
ADDCOLUMNS(
    newtab,
    "Re",
    var _loc = [Loc. No.]
    var _date = [Gen. Date]
    return
    IF(
        [S+U=24]=1,
        IF(
            ISBLANK([L1])||[flag]=0,
            1,
            1+
            COUNTROWS(
                FILTER(
                    newtab,
                    [Loc. No.]=_loc&&
                    [Gen. Date]<=_date&&
                    [flag]=1&&
                    NOT(ISBLANK([L]))
                )
            )
        )
    )
)
var _result = 
SUMX(
    SUMMARIZE(
        'Table',
        'Table'[Loc. No.],
        "x",
        SUMX(
            FILTER(
                t,
                [Gen. Date]=SELECTEDVALUE('Table'[Gen. Date])&&
                [Loc. No.]=EARLIER('Table'[Loc. No.])
            ),
            [Re]
        )
    ),
    [x]
)
return
IF(
    ISBLANK(_result),
    0,
    _result
)

 

Result:

a2.png

 

a3.png

 

 

Best Regards

Allan

 

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors