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
Anonymous
Not applicable

Show whole year in line graph while slicing on Month in a Year/Month Slicer

Hi members,

 

I have a line graph which is supposed to show Net Order Value development over the whole year, even when in my Year/Month Slicer only one Month is selected from a specific year.

 

E.g. Select Jan & Feb from 2021 => only 2021 Net Order Value Data (the whole year) should be displayed in the Line Graph.

 

The Year/Month slicer contains a Year column and  Month (Jan, Feb ...) column.

 

Is there a way to create a Dax Measure which can calculate Net Order Value

 

Net Order Value =
SUMX('Order','Order'[Order Value]-'Order'[Credit Note Value])

 

so that it calculates the value for the whole year based on the month selection in the Year/Month slicer?

 

Regards,

Mirza

2 REPLIES 2
Anonymous
Not applicable

@jaweher899 thank you for your quick reply.

How do you go about creating the filter? That part, honestly I did not understand. If I use Month Column and Year Column in one slicer in the report canvas, there is no way that the SUM of Sales = SUM(Sales [SalesAmount]) shows the selected month and the whole FY based on the month selection.
Here is what I get:

Mirza_A_0-1675115816101.png

 

I can only achive that when I create two separate filters, one month and the other year, and disable the interaction of the month filter with my line graph. In that way I can filter my line graph with the year filter and al other visuals with the month filter and year filter simultaniously.

 

 



jaweher899
Super User
Super User

To achieve this, the user can create a measure that calculates the sum of a certain column for a selected month and year, and use this measure in the line graph. Additionally, the user can create a filter that allows them to select a specific month and year to slice on.

Here's how:

  1. Create a measure: In the Fields Pane, select the column that you want to sum for a selected month and year, and create a measure using the SUM function. For example:

SUM of Sales = SUM(Sales[SalesAmount])

  1. Create a filter: In the Fields Pane, select the date column, and add a filter to the report canvas. In the filter, select the desired month and year to slice on.

  2. Use the measure in the line graph: In the Fields Pane, select the measure created in step 1, and add a line graph to the report canvas. In the line graph, use the date column as the X-axis, and the measure as the Y-axis.

By using this approach, the line graph will show the sum of the selected column for a selected month and year, allowing the user to view the data for a specific time frame while still seeing the overall trend for the whole year.

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.