powerbidaxdata-analysispowerbi-desktopmeasure

How to show timeseries data up to selected month in Power BI line chart with a Slicer


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

enter image description here


Solution

  • Steps as follows.

    Create a date table:

    Date = ADDCOLUMNS( CALENDARAUTO(), "Month Year Text", FORMAT([Date], "mmmm yyyy"), "Month Year", YEAR([Date])*1000 +  MONTH([Date]))
    

    enter image description here

    Create a relationship:

    enter image description here

    Replace your slicer with this field:

    enter image description here

    Sort Month Year Text but Month Year

    enter image description here

    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:

    enter image description here