Forum Discussion
Consecutive Weeks for Material
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
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 } ) )
3 Replies
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
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
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 } ) )
