Forum Discussion
"Time intelligence" for non-time dimension
- 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").
- 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,
Sure:
'NBA season' is the dimension table of the seasons (just a plain list of "Season" / "SeasonID").
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,