In Power BI, I created two visuals: a card visual, a time series line chart, and a month slicer.
I want the card visual to show data for the month selected in the slicer. For the line chart, I want it to display the timeseries for all the months up to the selected month. For example, if I have data until May 2024 and select March 2024 in the slicer, I want the line chart to show data from the start up to March 2024, ignoring April and May. Similarly, if I select December 2023 in the slicer, the line chart should show data until December 2023. Is this possible?
I managed to show all timeseries data for all months by disconnecting the slicer from the line chart using the Format > Edit interactions options. However, when I do this, the line chart displays data for all months, regardless of the slicer selection. I am wondering how I can make the line chart show data only up to the month selected in the slicer. Any help is greatly appreciated.
Sample Power BI file - Sample file
Steps as follows.
Create a date table:
Date = ADDCOLUMNS( CALENDARAUTO(), "Month Year Text", FORMAT([Date], "mmmm yyyy"), "Month Year", YEAR([Date])*1000 + MONTH([Date]))
Create a relationship:
Replace your slicer with this field:
Sort Month Year Text but Month Year
Create a measure
Sum To Date =
VAR x = MAX('Date'[Date])
RETURN
CALCULATE( SUM(SampleData[Value]), CROSSFILTER('Date'[Date], SampleData[MonthStartDate], None), KEEPFILTERS( SampleData[MonthStartDate] <= x))
Use the measure in your line chart: