More months on Bar Graph than on Slicer

[AntoineC]

Hi,

I have a dashboard with a date slicer used to select one month.

I also have a Bar Graph that displays the agregated value for this particular month. The value comes from a measure copied at the end of this post.

--> I want to select a single month on the slicer and have my Bar Graph show the results for the selected month + 11 months prior.

For instance if the slicer is set to 2022-07, I want the graph to show results from 2021-08 to 2022-07. And if the slicer is changed to 2021-12, I want the graph to show results from 2021-01 to 2021-12. The results would look like the screenshot below, obtained via forcing the date range in a filter in the graph and disconnecting interaction between slicer and graph.

I can't get my head around expanding the date range filtered by a slicer and using it in a graph... Someone knows how to do this ?

Bar Graph X-Axis on the graph is a calculated column from my dates table, with endofmonth dax formula.

Bar Graph Y-Axis comes from this measure, a bit complex because it manages different agregations according to a parameter table (T_FIL_PARAM_INDIC) :

VALUE =

VAR COES = MAX(T_FIL_PARAM_INDIC[CD_EDS])

VAR INDIC = SUBSTITUTE(MAX(T_FIL_PARAM_INDIC[CO_INDIC]),"_EFF","")

RETURN

IF(DISTINCTCOUNT(T_FIL_PARAM_INDIC[LIBELLE_INDIC])>1,BLANK(),

IF(

MAX(T_FIL_PARAM_INDIC[AGREGAT]) = "Somme",

SUM(T_FIL_COMPLET_Q[NUM])+0,

IF(

MAX(T_FIL_PARAM_INDIC[AGREGAT]) = "Ratio",

DIVIDE(SUM(T_FIL_COMPLET_Q[NUM]),SUM(T_FIL_COMPLET_Q[DENOM]))*100+0,

IF(

MAX(T_FIL_PARAM_INDIC[AGREGAT]) = "Moyenne",

DIVIDE(SUM(T_FIL_COMPLET_Q[NUM]),SUM(T_FIL_COMPLET_Q[DENOM]))+0,

IF(

MAX(T_FIL_PARAM_INDIC[AGREGAT]) = "Stock",

CALCULATE(SUM(T_FIL_COMPLET_Q[NUM]), LASTDATE(T_FIL_COMPLET_Q[DTJOUR]))+0,

IF(

MAX(T_FIL_PARAM_INDIC[AGREGAT]) = "Efficience",

DIVIDE(

CALCULATE(SUM(T_FIL_COMPLET_Q[NUM]), ALL(T_FIL_PARAM_INDIC[LIBELLE_INDIC],T_FIL_PARAM_INDIC[FREQUENCE],T_FIL_PARAM_INDIC[RANG]),T_FIL_PARAM_INDIC[CO_INDIC] = INDIC),

CALCULATE(SUM(T_FIL_COMPLET_Q[NUM]), ALL(T_FIL_PARAM_INDIC[LIBELLE_INDIC],T_FIL_PARAM_INDIC[FREQUENCE],T_FIL_PARAM_INDIC[RANG]),T_FIL_PARAM_INDIC[CO_INDIC] = "TR_" & COES & "_RH_01")

)+0

))))))

[PabloDeheza · Accepted solution]

Hi there!

You can refer to this video/post from SQLBI. 

https://www.youtube.com/watch?v=d8Rm7dwM6gc

Let me know if that helps!

[AntoineC]

Hi Pablo,

Thanks for the video, it's very close to what I'm looking for!

Now my chart show the 12 months I want, but it includes the names of all months available in my dates tables...

Any idea why ?

Thanks,

Antoine

[PabloDeheza]

Not sure exactly why that is happening, is it possible that one of your measures has a "+0" somewhere? If not, check the filter pane, it might be possible to remove those values from there.

[PabloDeheza]

Yeah i just double checked the measure above, the +0 on it might be the reason.

[AntoineC]

Removed the 0 and it works like a charm, thanks again !