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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mirza_A
Frequent Visitor

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

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors