count of consecutive weeks in given period

[Chava1881]

Hello,

Posting this for the second time as the first one just dissapered. 

I'm being asked to develop a tool that can help counting the many of times a component is coming consecutively in the shortage material list, understand if there's a way to call out last time it was called out as shortage and calculated of the many of weeks given, how many times out of the total possible it has been shortage, this way I can know:
1. How many weeks it has been in the shortage list

2. When was the last time it was considered a shortage
3. Understand if it is a frequent visitor on this list.

See example of measures needed below as well as how data is seen in dataset.

[v-yingjl]

Hi @Chava1881 ,

Based on your posted picture, have couple of questions want to know:

1. How did the [Consecutive occurrences] calculate?
2. How did the [Last week reported as shortage] confirm the date?
3. Under the occurrence% logic, why its value is not 0 when component is 4,6,7,8,9?

Could you please sharing more details about this issue for further discussion so that members in the community could help you better.

Best Regards,
Community Support Team _ Yingjie Li

[Chava1881]

Hello @v-yingjl 
I apologize in advance for sharing such a big explanation. Hope this helps clarify!

This is a weekly report for the time being, so if a component is reported as shortage on one week, it will reflect under the report date as such, if it isn't it will not be available. If you sort the data using a matrix for example, you may add the components as rows and the dates as columns then just drop on value let's say the quantity, so “behavior” is seen, you would be able to see that dates in which their values are not blanks are where a shortage was called out, if it is blank it means it was not part of the report on that report date

1. How did the [Consecutive occurrences] calculate? R/ If a component is reported as shortage on current week (12-15-2020) and on next week’s report (12-22-2020) is still on shortage, it will create a consecutive occurrence, the idea is to report that from current date in this case, as 12-22 would be the latest date available, it would have 2 occurrences in row and that’s what I need to count first, then the total in a given time.
2. How did the [Last week reported as shortage] confirm the date? R/ I think the use of last week might be understood literally as “last week”, I meant the last date a component reported was reported as shortage (my bad, not native)
3. Under the occurrence% logic, why its value is not 0 when component is 4,6,7,8,9? R/ those despíte of not being consecutively reported as shortage using 10-27 as latest date, in the 7 dates available they have been reported as such. I added what I meant as headers for the tables below:

Consecutive ocurrences: Using the latest date available, count how many weeks - going backwards - has a component consecutively been called out as shortage

Last week reported as shortage: it is literally the last date the component was called out as shortage

Ocurrences %: total count out of ocurrences during the given period, it doesn't matter whether it has been reported consecutively as shortage or has been in/out of the report divided by the total weeks available.

[v-yingjl · Accepted solution]

Hi @Chava1881 ,

Based on your description, you can create these three measures:

Consecutive occurrence = 
VAR _notblankdate =
    CALCULATE (
        MAX ( 'Table'[Report Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Component]
                IN DISTINCT ( 'Table'[Component] )
                    && 'Table'[Revenue Impact] <> BLANK ()
        )
    )
VAR _blankdate =
    CALCULATE (
        MAX ( 'Table'[Report Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Component]
                IN DISTINCT ( 'Table'[Component] )
                    && 'Table'[Revenue Impact] = BLANK ()
        )
    )
VAR _blank =
    IF (
        ISBLANK ( _blankdate ),
        CALCULATE (
            MAX ( 'Table'[Report Date] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Component] IN DISTINCT ( 'Table'[Component] )
            )
        ),
        _blankdate
    )
RETURN
    IF (
        _blank = _notblankdate,
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Component] IN DISTINCT ( 'Table'[Component] )
            )
        ),
        IF ( _notblankdate < _blank, 0, DATEDIFF ( _blank, _notblankdate, WEEK ) )
    )

Last week reported as shortage = 
CALCULATE (
    MAX ( 'Table'[Report Date] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Component]
            IN DISTINCT ( 'Table'[Component] )
                && 'Table'[Revenue Impact] <> BLANK ()
    )
)

occurrences% = 
VAR _rows =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Component] IN DISTINCT ( 'Table'[Component] )
        )
    )
VAR _count =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Component]
                IN DISTINCT ( 'Table'[Component] )
                    && 'Table'[Revenue Impact] = BLANK ()
        )
    )
RETURN
     ( _rows - _count ) / _rows

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Replacing the entries in a Total column with a text entry like "5 out of 7" (which will be a text data type) will be complicated (if at all it is even possible to solve).  Atbest, we can get yoru desired result in another table/matrix visual. 

[Chava1881]

Hello, the "5 out 7" was just added there to explain how I got the percentage, I care more about counting times a component is coming up as shortage consecutively and calling out the last time it was reported as shortage. 

[Ashish_Mathur]

Hi,

Here are my question:

1. For components 5 and 10, while the % should be 100%, should it be 7/7?
2. For components 7,8 and 9, shouldn't the % be 5/7 i.e. 71%

[Chava1881]

Hello, my answers below:

1. For components 5 and 10, while the % should be 100%, should it be 7/7? R/ made a mistake with the total count, you are right it is 7-7
2. For components 7,8 and 9, shouldn't the % be 5/7 i.e. 71%. R/ yes, there I probably copied and pasted the the 4th record instead of the first one which was the one reflecting your question. Another mistake on my end.