cancel
Showing results for
Did you mean:
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
Frequent Visitor

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:

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.

Impactful Individual

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.

Announcements

#### Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors