topic All months to the one selected in slicer in DAX Commands and Tips

All months to the one selected in slicer

gumis_rulez — Mon, 12 Aug 2024 17:56:16 GMT

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

Re: All months to the one selected in slicer

lbendlin — Mon, 12 Aug 2024 23:35:26 GMT

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.

Re: All months to the one selected in slicer

Anonymous — Tue, 13 Aug 2024 02:10:05 GMT

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.