Anonymous

Not applicable

6 years ago

Solved

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

Show and Tell

v-alq-msft

6 years ago

Hi, Anonymous

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

Table:

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:

Best Regards

Allan

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

Count consecutive days based on selected date by location.pbix62 KB

7 Replies

amitchandak
Super User
6 years ago
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
  6 years ago
  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?
  - Greg_Deckler
    Community Champion
    6 years ago
    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)
    
    working67.pbix757 KB

Greg_Deckler

Community Champion

6 years ago

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.

Anonymous

Not applicable

6 years ago

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

Gen. Date	Loc. No.	GF	FM	S	U	S+U=24
01-Apr-20	123	0.00	5.80	0.00	0.50	0
02-Apr-20	123	0.00	12.80	0.00	1.00	0
29-Apr-20	123	3.70	0.00	12.00	12.00	1
30-Apr-20	123	4.70	0.00	12.00	12.00	1
01-May-20	123	0.00	0.00	12.00	12.00	1
02-May-20	123	0.00	0.00	0.00	0.00	0
07-May-20	123	0.00	0.00	0.00	0.00	0
08-May-20	123	0.00	0.00	11.00	13.00	1
30-May-20	123	0.00	0.00	0.00	0.00	0
31-May-20	123	0.80	0.00	0.00	0.00	0
01-Jun-20	123	0.00	0.00	0.00	0.00	0
02-Jun-20	123	0.00	0.00	0.00	0.00	0
03-Jun-20	123	0.00	0.00	0.00	0.00	0
04-Jun-20	123	0.00	0.00	0.00	0.00	0
08-Jun-20	123	0.00	0.00	0.00	0.00	0
12-Jun-20	123	0.00	0.00	0.00	0.00	0
30-Jun-20	123	3.60	0.00	8.00	16.00	1
01-Apr-20	212	4.40	0.00	0.00	0.00	0
02-Apr-20	212	1.40	0.00	0.00	0.00	0
29-Apr-20	212	0.20	0.00	0.00	0.00	0
30-Apr-20	212	0.00	0.00	0.00	0.00	0
01-May-20	212	1.50	1.00	0.00	0.60	0
02-May-20	212	0.00	0.00	0.00	0.00	0
07-May-20	212	1.00	0.00	0.00	0.00	0
08-May-20	212	0.20	0.10	0.00	0.00	0
30-May-20	212	12.00	12.00	0.00	0.00	0
31-May-20	212	12.00	12.00	0.00	0.40	0
01-Jun-20	212	8.00	16.00	0.00	0.00	0
02-Jun-20	212	5.00	19.00	0.00	0.00	0
03-Jun-20	212	0.00	0.00	0.00	0.00	0
04-Jun-20	212	0.00	0.00	0.00	0.00	0
05-Jun-20	212	12.00	12.00	0.00	0.00	0
08-Jun-20	212	12.00	12.00	0.00	0.00	0
12-Jun-20	212	0.00	0.00	0.00	0.00	0
30-Jun-20	212	11.00	13.00	10.00	14.00	1

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

v-alq-msft

Community Support

6 years ago

Hi, Anonymous

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

Table:

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:

Best Regards

Allan

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

Count consecutive days based on selected date by location.pbix62 KB

Forum Discussion

Count consecutive days based on selected date by location

7 Replies

Related content

periods of consecutive instances

DAX Count consecutive

Tool tip Location

Roster by Location

Fabric Capacity location != Azure DevOps location