Dynamic bar chart per month depending on slicer selection

[JanD]

Hi everyone, 

I am looking for an advise here. I have actual data currently up to March 2024 and Forecast per every month available. 

I need to visualize data on a bar chart per month, in a way that the chart will change based on single select slicer (month) on that page. 

Meaning if I select February on the slicer, the bar chart will show January and February actual data, but Forecast for the rest of the monhts. 

If I select June, the bar chart will show January, February and March actual data, but the rest as there is no actual available should be Forecast. 

Thank you for your help in advance!

[Anonymous · Accepted solution]

Hi, @JanD 

As you mentioned, you have actual data and forecast data, and I created a sample data as follows:

First, I created a date table based on this table, as shown in the following image:

Use the Date column of this table as the slicer option:

Use the following DAX expressions to dynamically calculate the actual data before the slicer selected month:

Y axis =
VAR _seletmonth =
    CALCULATE (
        MAX ( 'Table 2'[Date] ),
        SELECTCOLUMNS ( 'Table 2', 'Table 2'[Date] )
    )
RETURN
    IF (
        MAX ( 'Table'[Dates] ) <= _seletmonth,
        CALCULATE ( SUM ( 'Table'[Actual data] ), 'Table'[Dates] <= _seletmonth ),
        CALCULATE ( SUM ( 'Table'[Forecast data] ), 'Table'[Dates] >= _seletmonth )
    )

The stacked column chart visual is set as follows:

When I select January, January corresponds to the actual data, and February and beyond are the forecast data.

When I select February, January-February is the actual data, and the following months are the forecast data:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.