Trend lines by month - display one of the lines for a few months only

[Anonymous]

Hi all,

Well, the title may not be clear, I apologize.

Context :

I am displaying some variations on a line chart.
Basically my sales for the year compared to previous year (Y vs Y-1) and my budgeted sales compared to my sales realised this year (Y vs BU).
Quite simple 🙂
I have a new estimate of my budget in July (BU2). A new line is added (Y vs BU2), which display data only from July.

What I am trying to achieve :

In order to clean my visuals, I would like to display Y vs BU2 only for months following July.

I therefore built some measure to add a filter on month.

It works very well on my BU2 value with the following measure.

Sales BU2 (July to Dec only) =

CALCULATE( [Sales(BU2)];

     FILTER(VALUES('Dates'[Month MM]);'Dates'[Month MM] >= 7 ))

But once I calculate my gap (Y vs BU2), the data for previous month seems to be used for calculations and appear.
Here are the formula and the related screenshot

Sales Y vs BU2 (Jul to Dec only ) =

DIVIDE([Sales (Y)]; [Sales BU2 (July to Dec only)]) - 1

I tried to filter my Y Sales in an other measure with the same measure (FILTER(VALUES('Dates'[Month MM]);'Dates'[Month MM] >= 7 ))) prior calculations. It works in value, but not in my calculation again.

In order to circumvent this, I also tried to apply directly my Filter in the versus measure :

Sales Y vs BU2 (Jul to Dec only ) =

CALCULATE(DIVIDE([Sales (Y)]; [Sales BU2 (July to Dec only)]) - 1;

FILTER(VALUES('Dates'[Month MM]);'Dates'[Month MM] >= 9 ))

But it does not work too ...

Do you have some ideas to solve my issue ??

Thanks in advance for your help.

Benjamin

[v-danhe-msft]

Hi @Anonymous ,

What is your desired result? If you just want to show the data between August and December, could you have tried with the visual slicer in power bi:

Regards,

Daniel He

[Anonymous]

hi @v-danhe-msft 

Thanks for answering.

I want to show a the data between January and December on one trend, and the data between August and December for another one. Therefore I can't apply a visual filter...

My desired result would be this, what I'm trying to achieve is remove the data in red. My formula calculate my variations only between August an December but there are still these remaining data on previous months.

Thanks for your help.

Benjamin

[Anonymous · Accepted solution]

Hi,

This issue got solved.

Sales Y vs BU2 (Jul to Dec only ) =
DIVIDE([Sales (Y)]; [Sales BU2 (July to Dec only)]) - 1

Simply, in my formula, the filters are not applied on the "-1". That is why there was -100% appearing...

Thus, I used this formula :

DIVIDE([Sales (Y)] - [Sales BU2 (July to Dec only)]; [Sales BU2 (July to Dec only)])

Benjamin