Forum Discussion

Termigez's avatar
Termigez
Regular Visitor
6 years ago
Solved

Check for value

datehomeaway
17.10.2018af
18.10.2018ab
19.10.2018fc

 

I want to create a new calculate column to check whether a value in 'home' column is present in either 'home' column or 'away' column only for rows with yesterday's date. So for example for a game on 19.10.2018 the formula would return blank because 'f' didn't play yesterday - his last game was 2 days ago. But for a game on 18.10.2018 the formula would return something ("yes", "1") etc. because 'a' indeed played a game on 17.10.2018. Thank you!

Need Help
  • az38's avatar
    az38
    6 years ago

    Termigez 

    sorry, it was my bad

     

     

    Column = 
var _playedYesterday = countrows(filter('Table','Table'[date]=EARLIER('Table'[date])-1 && ('Table'[home]=EARLIER('Table'[away]) || 'Table'[home]=EARLIER('Table'[home]))))
return
if(_playedYesterday>0,"yes","no")

     

     

    should be correct for back-to-back games for home team 

    I love sports stats! 🙂

8 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    hi Termigez 

    try a column

    Column = 
var _playedYesterday = countrows(filter('Table','Table'[date]=EARLIER('Table'[date])-1 && ('Table'[away]=EARLIER('Table'[away]) || 'Table'[home]=EARLIER('Table'[home]))))
return
if(_playedYesterday>0,"yes","no")
    • Termigez's avatar
      Termigez
      Regular Visitor
      6 years ago

      Hi, it is super close! However right now this formula doesn't tell me whether home team or away team played yesterday. It returns "yes" if one of those teams played yesterday but doesn't specify which one. I would like to verify this condition just for a home team, whether it played yesterday as a home team or away team. 

      • az38's avatar
        az38
        Community Champion
        6 years ago

        Termigez 

        it is exactly what you asked for in the first post.

        give me example of desired output, please, to complete solution 🙂