Consecutive Weeks for Material

[jordanxie]

Hi,

Want to create a measure to be used as a card view that counts the consecutive weeks a material is present. The format for the date is Yr-Wk# (2019-46). 

Let's say we are in Wk 48 so the card should display count for Material 1 as 2 (2019-47 & 2019-48), Material 2 as 1 (becuase it is not present in 2019-47 even though it appeared in 2019-45 & 2019-46)

Material         Yr-Wk#

1                    2019-45

2                    2019-45

2                    2019-46

1                    2019-47

1                    2019-48

2                    2019-48

Any help is appreciated. Thanks in advance

[v-eachen-msft · Accepted solution]

Hi @jordanxie ,

You could use the following measure:

Measure =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        VALUE ( RIGHT ( 'Table'[Yr-Wk#], 2 ) )
            IN { WEEKNUM ( TODAY (), 1 ), WEEKNUM ( TODAY (), 1 ) - 1 }
    )
)

[TomMartens]

Hey,

can you please describe the business rule more detailed, I have difficulties to understand why material 2 get a 1 even if there are 2 two consecutive events in the past.

Assuming that the week is selected by a slicer.

What would be the value for material 2 is there is a value for week 2019-47?

Regards,

Tom

[jordanxie]

Hi,

At this point we are more concern based on the latest snapshot (Week 48). There would not be a slicer for the week. 

If there was a a material 2 for 2019-47, then the count would be 4