How do I count discontinued dates in PowerBI?

[LeonardSchwenk]

I want to count the discontinued dates per ID with filter "FilterByValue" by 1.

What I mean by discontinued dates.

• 04.01.2021
• 05.01.2021
• 06.01.2021
• 08.01.2021

07.01.2021 date would be missing to be a continued date when a day between dates is missing its discontinued.

Dates have also to be distinct(see Row ID 2,3) and within the last 90 Days.

RowID is just for explanation purposes.

RowID	ID	FIlterByValue	Date
1	1	1	Monday, 4. January 2021
2	1	1	Tuesday, 5. January 2021
3	1	1	Tuesday, 5. January 2021
4	1	1	Wednesday, 6. January 2021
5	1	1	Monday, 11. January 2021
6	1	99	Friday, 8. January 2021
7	2	1	Tuesday, 9. February 2021
8	2	1	Wednesday, 10. February 2021
9	2	1	Thursday, 11. March 2021
10	2	1	Friday, 12. March 2021
11	2	1	Monday, 15. March 2021
12	2	1	Tuesday, 16. March 2021
13	2	99	Sunday, 14. March 2021
14	2	1	Wednesday, 14. April 2021

What I want to achieve:

RowID	ID	CountDIscontinuedDates
1	1	2
2	2	4

What I tried, I think is a bad/ not helping approach:

discontinuesDates = CALCULATE(COUNT(TableName[ID]), FILTER(TableName, TableName[FilterByValue]=34 && TableName[ID] = EARLIER( TableName[ID]) && DATEADD( TableName[Date],1,DAY) <> EARLIER( TableName[Date])) ) 

discontinuesDates = COUNTAX(FILTER(TableName, [ID]=1 && TableName[Date] > (TODAY()-90) && OR (DATEADD( TableName[Date] = (TableName[Datum],1,DAY), DATEADD( TableName[Date] = (TableName[Datum],-1,DAY) ) && TableName[ID] = EARLIER(TableName[ID]) && TableName[Date] = TableName[Date] ), TableName[ID])

[v-cazheng-msft · Accepted solution]

Hi @LeonardSchwenk 

Please add an index column in Power Query and  create these measures.

1 Judge whether later date is one day greater than previous day

IsDiffOnePrev =

VAR IsRecentDate =

    CALCULATE (

        DISTINCTCOUNT ( 'Table'[Date] ),

        FILTER (

            ALLEXCEPT ( 'Table', 'Table'[ID] ),

            'Table'[Date]

                = MAX ( 'Table'[Date] ) - 1

        )

    )

RETURN

    IF ( ISBLANK ( IsRecentDate ), 0, IsRecentDate )

2 Judge whether previous day is one day earlier than next day

IsDiffOneDayNext =

VAR val_ =

    CALCULATE (

        DISTINCTCOUNT ( 'Table'[Date] ),

        FILTER (

            ALLEXCEPT ( 'Table', 'Table'[ID] ),

            'Table'[Date]

                = MAX ( 'Table'[Date] ) + 1

        )

    )

RETURN

    IF ( ISBLANK ( val_ ), 0, val_ )

3 Count Continue Dates

CountContinuedDates =

CALCULATE (

    COUNT ( 'Table'[Index] ),

    FILTER (

        'Table',

        ( [IsDiffOneDayNext] = 0

            && [IsDiffOnePrev] = 1 )

            || ( [IsDiffOneDayNext] = 0

            && [IsDiffOnePrev] = 0 )

    )

)

Then, the result should look like this.

Also, attached the pbix file as reference.

Best Regards,

Community Support Team _ Caiyun

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[amitchandak]

@LeonardSchwenk , Try a measure like, And that you should use with ID, Row ID you should use min or max

Measure =

var _max = eomonth(maxx(filter(allselected(Table), [ID] = max(Table[ID])) , Table[Date]),0) // or use today
var _min = eomonth(minx(filter(allselected(Table), [ID] = max(Table[ID])) , Table[Date]),-1)+1
return
datediff(_min, _max, month)+1 - distinctCOUNT(Table[Date])

or

Measure =

var _max = eomonth(max( Table[Date]),0) // or use today
var _min = eomonth(min(Table[Date]),-1)+1
return
datediff(_min, _max, month)+1 - distinctCOUNT(Table[Date])

[LeonardSchwenk]

@amitchandak 

Thanks. But it doesnt seems to be the right fit for my question or I cant undestand it correctly yet.
RowID is not existing, it was just for showcase purposes. 
I dont see how min max with eomoth should work.

As there are mutliple dates in a month which can be discontined.
Just if a day between dates is missing its counted not on a monthly basis.
Its not just 2 ID its more like 16k ID. So im not sure what Max Table[ID] should do.

The filter for FilterByValue is also missing.

Very appreciate your help but it think what I mean with discontinued date is not yet unterstood correct ?

[amitchandak]

@LeonardSchwenk , My formula is based on assumption that you want to know missing dates for each ID.

So have taken Max date or we can take today for each ID, and then date diff (Min to Max) will give me duration and distinct available dates and diff of these two, should give missing dates count. By mistake, I used month in place of day

Measure =

var _max = eomonth(maxx(filter(allselected(Table), [ID] = max(Table[ID])) , Table[Date]),0) // or use today
var _min = eomonth(minx(filter(allselected(Table), [ID] = max(Table[ID])) , Table[Date]),-1)+1
return
datediff(_min, _max, day )+1 - distinctCOUNT(Table[Date])

or

Measure =

var _max = eomonth(max( Table[Date]),0) // or use today
var _min = eomonth(min(Table[Date]),-1)+1
return
datediff(_min, _max, day)+1 - distinctCOUNT(Table[Date])

[LeonardSchwenk]

Hi @amitchandak,

Thanks a lot for your help!

But thats sadly not what I want. I try to be more specific.
I dont want to know the missing dates!
I want to count the dates which are coherent.
Another example:

ID	FIlterByValue	Date
1	1	Monday, 4. January 2021
1	1	Monday, 4. January 2021
1	1	Tuesday, 5. January 2021
1	1	Monday, 5. April 2021
1	1	Tuesday, 6. April 2021
1	99	Friday, 7. May 2021
1	1	Sunday, 1. August 2021
1	1	Monday, 2. August 2021
1	1	Tuesday, 3. August 2021
1	1	Wednesday, 8. December 2021
1	1	Thursday, 9. December 2021
1	1	Friday, 10. December 2021
1	99	Saturday, 11. December 2021

What I want:

ID	CountDiscontinuedDates
1	5

Count: 1 

- 04.01.2021
- 04.01.2021

- 05.01.2021

Count: 2

- 05.04.2021

- 06.04.2021

Count 3:

- 07.05.2021

Count 4:

- 01.08.2021

- 02.08.2021

- 03.08.2021

Count 5:

- 08.12.2021

- 09.12.2021

- 10.12.2021

Between each count there is a gap >= Day.

If between dates date is >= 1, Day then
counter +1

Hopefully this makes it more clear.

[amitchandak]

@LeonardSchwenk , I did that something similar in the continuous streak. Not sure I counted those.  But all based on column, and then a measure 

Abstract Thesis Part 43: - Continuous streak : https://youtu.be/GdMcwvdwr0o

https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1...

Video and blog have some difference