Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
Tips and Tricks
  • tamerj1's avatar
    tamerj1
    4 years ago

    "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 )
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

"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 )