All months to the one selected in slicer

[gumis_rulez]

Hi,

I am trying to make following column graph:

- after choosing the year on the slicer and choosing the month I would like to show a bar chart with sales data from current year with all previous months.

F.e. after setting on slicer 2017 and March, would like to see like this:

After choosing f.e. June 2017, it should show January, February, March, April, May, June sales data.

How to achieve this?

1. I have removed the month slicer interaction on the graph.

2. I am using following measure

Sales 2 =

Var ChoosenMonth = SELECTEDVALUE('Calendar'[Month_ID])

VAR ChoosenYear = SELECTEDVALUE('Calendar'[Year_ID])

RETURN

CALCULATE(

    [Total Sales],

        'Calendar'[Year_ID] = ChoosenYear,

        'Calendar'[Month_ID] <= ChoosenMonth

        )

and the result is as follows:

It seams like Variable ChoosenMonth is not working, as it shows correcly year, but not correctly month.

On the second hand if I turn on the month slicer interaction on the column chart, the measure shows only data for choosen year and choosen month.

File: https://drive.google.com/file/d/12msE_DoQPCPszt3y1-fK94wJrc2P_BeD/view?usp=sharing 

[v-tianyich-msft · Accepted solution]

Hi @gumis_rulez ,

As lbendlin said, you need to go through an unrelated date table if you need to fulfill that requirement, though that also means that all your measure need to be changed.

Table = SUMMARIZE('Calendar',[Month_ID],[Year_ID])

Sales 2 = 
Var ChoosenMonth = SELECTEDVALUE('Table'[Month_ID])
VAR ChoosenYear = SELECTEDVALUE('Table'[Year_ID])

RETURN
CALCULATE(
    [Total Sales],
        'Calendar'[Year_ID] = ChoosenYear,
        'Calendar'[Month_ID] <= ChoosenMonth
        )
        

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[lbendlin]

Make sure this does not confuse your users.  Better use a "Before"  slicer.

You need a disconnected table to feed your slicer , and measures to calculate the running total/monthly values.