Forum Discussion

fpennisi's avatar
fpennisi
Regular Visitor
1 year ago
Solved

"Time intelligence" for non-time dimension

Hi, I'm working with a NBA Dataset.  I have a table showing the season, where a Season as an ID which is the starting year of the season (e.g. for season 2023/24 the ID is 2023).   I would like t...
  • fpennisi's avatar
    fpennisi
    1 year ago

    Sure:

     

    MIN = AVERAGE('NBA box score - Player'[TMIN])
    Selected season = SELECTEDVALUE('NBA season'[SeasonID])
     
    'NBA box score - Player' is the table with player stats by game.

    'NBA season' is the dimension table of the seasons (just a plain list of "Season" / "SeasonID").

  • DataNinja777's avatar
    DataNinja777
    1 year ago

    Hi fpennisi ,

     

    Thank you for the clarification. I encountered a similar issue where the previous month's values didn’t appear when the current month filter was applied in the row fields. I believe the formula below will resolve this issue. 

    EoPS MIN Previous Season = 
    VAR CurrentSeason = SELECTEDVALUE('NBA season'[Season ID])
    VAR PreviousSeason = CurrentSeason - 1
    RETURN
        CALCULATE(
            [MIN],
            FILTER(
                ALL('NBA season'),
                'NBA season'[Season ID] = PreviousSeason
            )
        )
    

     

    The reason the previous formula returned blank rows is that when you use a matrix visual with the current season in the row field, Power BI automatically applies a filter to show only the current season. This means that for each row in the visual, only data for the selected season is considered. The filter context created by the visual restricts the data to the specific season in that row.

    Your original formula was constrained by this filter context, which applied only to the current season.

     

    Best regards,