Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AntoineC
Frequent Visitor

More months on Bar Graph than on Slicer

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.

 

AntoineC_0-1663592567582.png

 

--> 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.

 

AntoineC_1-1663593457176.png

 

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
))))))

 

1 ACCEPTED SOLUTION
PabloDeheza
Solution Sage
Solution Sage

Hi there!

You can refer to this video/post from SQLBI. 

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

Let me know if that helps!

View solution in original post

5 REPLIES 5
PabloDeheza
Solution Sage
Solution Sage

Hi there!

You can refer to this video/post from SQLBI. 

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

Let me know if that helps!

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 ?

 

AntoineC_1-1663595945989.png

 

Thanks,

Antoine

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.

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

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.