Forum Discussion
Calculating short using row context.
Hi All,
I have been struggling to get this measure done. I badly need help with this.
So the use case here is I need to calculate the Short measure. Coming to the actual struggle. As you can see in the table for the same Product and location the dates and the End_inv is different. Whenever the end_inv is negative the shortage is Yes and positive No. I need to calculate the Short in the following manner:
Short = when shortage is No it is 0 and the next row after 0 should be 1 i.e. if there is a Yes after a No then it should return 1 but all the Shortage= Yes should not be 1.
| Product | Location | Date | End_inv | Shortage | Short |
| 400269 | 1110 | 08-10-2022 | 30 | No | 0 |
| 400269 | 1110 | 08-11-2022 | -2 | Yes | 1 |
| 400269 | 1110 | 08/13/2022 | 10 | No | 0 |
| 400269 | 1110 | 08/15/2022 | 3 | No | 0 |
| 400270 | 1110 | 08/14/2022 | -10 | Yes | 1 |
| 400270 | 1110 | 08/15/2022 | -5 | YEs | 0 |
| 400270 | 1110 | 08/16/2022 | 5 | No | 0 |
| 400270 | 1110 | 08/18/2022 | 6 | No | 0 |
Hope you understood the query.
Note: End_inv, Shortage are measures.
Thanks in advance.
tamerj1 JihaiXia Henrykong_
"Hi Anonymous
assuming [Sortage] is a measure, please try
Short = VAR CurrentDate = SELECTEDVALUE ( 'Date'[Date] ) VAR CurrentShortage = [Shortage] VAR T1 = CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[Product], TableName[Location] ) ) VAR T2 = FILTER ( T1, TableName[Date] < CurrentDate ) VAR PreviousDate = MAXX ( T2, TableName[Date] ) VAR PreviousShortage = CALCULATE ( [Shortage], 'Date'[Date] = PreviousDate, T1 ) RETURN IF ( CurrentShortage = "Yes" && PreviousShortage <> "Yes", 1, 0 )
1 Reply
- tamerj1
Community Champion
"Hi Anonymous
assuming [Sortage] is a measure, please try
Short = VAR CurrentDate = SELECTEDVALUE ( 'Date'[Date] ) VAR CurrentShortage = [Shortage] VAR T1 = CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[Product], TableName[Location] ) ) VAR T2 = FILTER ( T1, TableName[Date] < CurrentDate ) VAR PreviousDate = MAXX ( T2, TableName[Date] ) VAR PreviousShortage = CALCULATE ( [Shortage], 'Date'[Date] = PreviousDate, T1 ) RETURN IF ( CurrentShortage = "Yes" && PreviousShortage <> "Yes", 1, 0 )