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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

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
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

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.

 

Greg_Deckler
Super User
Super User

@Anonymous - 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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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

amitchandak
Super User
Super User

@Anonymous , 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)

Anonymous
Not applicable

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?

@Anonymous - 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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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