Forum Discussion
Cumulative filtering using date month
Hi,
I'm triying to filter a time series that represents a cumulated metric based on the month of the metric. I'm using the fiscal year so the possible values for the filter are 202211, 202212 ... 202310. However, this column called 'Year month' is of character type since we also have values for quarters (like q1, q2, etc), but are excluded for this visualization.
I would like this filter to be a dropdown menu (not a slicer), but when one month is selected in the filter, the visualization should still include the months before. For example, if I select the month 202301 in the dropdown menu, the time series should display the evolution of the metric for month 202211, 202212 and 202301. I know it's possible to select multiple values ina dropdown menu, but in this case, in order to reduce manipulations for users to a minimum, I would like this to happen while only selecting one value.
Do you have any suggestions?
1 Reply
Cedric_bn , Make sure the distinct month year is in a separate table(Say Date) . It will join with month year of your table if you need rolled-up value under the current month name
This table will not join if you need a trend
calculate(Sum(Table[Value]), filter(all(Date), Date[Month Year] <= Max( Date[Month Year] ) ))
for trend, the table will not join
calculate(Sum(Table[Value]), filter((Table), Table[Month Year] <= Max( Date[Month Year] ) ))
