Show last years in a chart

[PwrBI01]

Hi guys,

I have this chart and I would like to just show the last 2 years, is there a way to do it automatically instead of using filters for the chart?

The file is in this url: https://www.dropbox.com/s/l5a8cmv5oarrktk/Sales.pbix?dl=0

Thanks in advance.

Regards.

[Ashish_Mathur]

Hi,

Does my solution here help? - Flex a Pivot Table to show data for x months ended a certain user defined month.

[PwrBI01]

Hi @Ashir, thank you very much. I needed to show always the same month (not the previous ones). Anyway, that link is very useful and I am sure I will use it later for another visuals.

[v-eqin-msft · Accepted solution]

Hi @PwrBI01 ,

It seems that you want to show the values of  last 2 year based on the max date in Calendar table, right?

You could use the following formula to create a measure :

Measure=
VAR _maxYear =
    MAXX ( ALL ( 'Calendar table' ), YEAR ( [Date] ) )
RETURN
    IF ( _maxYear - YEAR ( MAX ( 'Sales'[Date] ) ) IN { 0, 1 }, 1, 0 )

After applying it to the filter pane ,the chart could be dynamically changed when new data like 2021 added:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PwrBI01]

Hi @v-eqin-msft, I have add values until May 2021 and I have a new question.

If I select the month December it just shows the year 2020 (because there is a filter for the last two years and there are no data for December 2021).

Is it possible to filter the last two years with data? So if I select December it shows December 2019 and December 2020 and if I select May it shows May 2020 and May 2021

Thanks in advance.

Regards.

[v-eqin-msft]

Hi @PwrBI01 ,

It's suggested post a new thread since this has been resolved. 

More engineers will answer for you as soon as possible.😀

And please provide necessary information such as tables and expect outputs.

Best Regards,
Eyelyn Qin

[PwrBI01]

Hi @v-eqin-msft,

Thank you for your answer. I have post it with the new problem on https://community.powerbi.com/t5/Desktop/Show-last-2-years-with-data-in-a-chart/m-p/1915142#M732288

Thanks in advance.

Regards.

[PwrBI01]

Hi @v-eqin-msft, that is exactly what I need, thank you very much.

[VijayP]

@PwrBI01 

I have got this solution with small manual intervention. However will work out the final out come as well
check this pbix

[PwrBI01]

Thank you very much @VijayP,

I will try continue looking for a way with no manual intervention, but if there is not possible I will use this one.

Regards.

[VijayP]

@PwrBI01 If you find this as solution please mark this as solution . Thanks

[VijayP]

@PwrBI01 remove the slicers to see the result in the 2nd visual

[VijayP]

@PwrBI01 

"I have this chart and I would like to I have this chart and I would like to just show the last 2 years, is there a way to do it automatically instead of using filters for the chart? , is there a way to do it automatically instead of using filters for the chart?"

That was your question hence created the measure. It is giving last two years . you can remove the slicer and see the result. you can change the number in the formula and see the number of years increasing! or decreasing

[PwrBI01]

Hi @VijayP,

I'm sorry if I explained in a wrong way. What I want is to get the chart on the right part automatically with a measure, so it should show the years 2019 and 2020 with the data that are charged and if for example I add 2021 and I don't delete any year it should show the years 2020 and 2021.

If I do this with a filter on the chart, I have to change the filter every time I add a new year.

I send you the photo of what I want:

Regards.

[VijayP]

@PwrBI01 

test = CALCULATE([YTD-Sales],FILTER(ALL('Calendar table'),'Calendar table'[Year]<=MAX('Calendar table'[Year])-4))

use this formula

and pbix file is attached here

[PwrBI01]

Hi @VijayP,

Thank you for your answer.

I tried to download the pbix you sent but I think you confused and you sent the same as me.

When I created that measure it shows the last 2 years, but the results aren't the same.