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

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 IV
Super User IV

@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 IV
Super User IV

@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

@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

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

@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

@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

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

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors