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
Georg
Frequent Visitor

Dynamic x axis - show only January - selected month

 

PBI dynamic x axis.PNG

Hi Experts,

is there a way to show only January - selected month figures? E.g. I would like to see January - June figures only if June is the selected month (selecting all month January through June is not an option).

 

Kind regards,

Georg

1 ACCEPTED SOLUTION
Dog
Responsive Resident
Responsive Resident

Hi, 

 

you could look at creating new measure that specifically looks at the selected filter and takes that as the max date but overrides the filter on the calculate

This is assuming that you have a Dates table for filtering. (if no dates table let me know what you have got) 

 

ActualToSelectedMonth:=

var MaxDate = MaxDates([DateKey])

return

CALCULATE (
[Actual], ALL('Dates')
DATESYTD ( Dates[Datekey], "01/31" ),
Dates[DateKey] < MaxDate
)

-------

(the DATESYTD will need your month end day and month in there to know when to start from....) have edited to January for now. 

 

in theory this should replace the current context filter on Month (again assuming it's in the dates table) for [actual] but still using it as a Max filter. 

 

I've not got the same data as you so I'm hoping the above makes sense and assists.

 

View solution in original post

4 REPLIES 4
Dog
Responsive Resident
Responsive Resident

Hi, 

 

you could look at creating new measure that specifically looks at the selected filter and takes that as the max date but overrides the filter on the calculate

This is assuming that you have a Dates table for filtering. (if no dates table let me know what you have got) 

 

ActualToSelectedMonth:=

var MaxDate = MaxDates([DateKey])

return

CALCULATE (
[Actual], ALL('Dates')
DATESYTD ( Dates[Datekey], "01/31" ),
Dates[DateKey] < MaxDate
)

-------

(the DATESYTD will need your month end day and month in there to know when to start from....) have edited to January for now. 

 

in theory this should replace the current context filter on Month (again assuming it's in the dates table) for [actual] but still using it as a Max filter. 

 

I've not got the same data as you so I'm hoping the above makes sense and assists.

 

v-huizhn-msft
Employee
Employee

Hi @Georg,

You can create a slicer including Month field, when you select one month, it will shows 

 

I have the sample data table.

1.PNG

Create a chart, please see the following screenshot.

2.PNG

Finally, you can create a slicer including Month field, when you select Jan, it will show only Jan and corresponding Sales.

Capture11.PNG

If you have other issues, don't hesitate to let me know.

Best Regards,
Angelia





Hi Angelia,

thanks for your answer. I do have already a slicer, the month above the chart. And if I click on Jun, the June value is shown as expected. But this is not what I cant to achieve. In case of selecting Jun, figures from Jan - Jun shall be shown in the chart.

 

kind regards,

Georg

Hi @Georg,

Got it. As I tested, we are unable to achieve by slicer, When you select Jun, we can ontain the sum of Jan-Jun. But we can't display Jan-Jun in the chart.

Best Regards,
Angelia

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

Find out what's new and trending in the Fabric Community.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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