cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PwrBI01
Post Patron
Post Patron

Show last 2 years with data in a chart

Hi guys,

 

I have the following chart and I would like to show the last 2 years with data automatically:

 

PwrBI01_0-1624374374487.png

 

In a post I did some days ago: https://community.powerbi.com/t5/Desktop/Show-last-years-in-a-chart/m-p/1886634#M724593 

I had dates until december and I tried to show the last 2 years with an automatically measure.

 

Thanks to your help I got a way to do it using the following measure:

Measure=

VAR _maxYear =

    MAXX ( ALL ( 'Calendar table' ), YEAR ( [Date] ) )

RETURN

    IF ( _maxYear - YEAR ( MAX ( 'Sales'[Date] ) ) IN { 0, 1 }, 1, 0 )

 

The problem I have now is that 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.

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @PwrBI01 ,

I create some data:

vyangliumsft_0-1624584659204.png

Here are the steps you can follow:

1. Create measure.

Flag =
var _selectedmonth=SELECTEDVALUE('Calendar table'[Month number])
var _maxyear=YEAR(MAXX(ALL(Sales),[Date]))
var _data=SUMX(FILTER(ALL(Sales),YEAR([Date])=_maxyear&&MONTH([Date])=_selectedmonth),[Sales])
return
IF(
    _data=BLANK(),
    IF(YEAR(MAX('Sales'[Date]))>=_maxyear-2&&YEAR(MAX('Sales'[Date]))<=_maxyear-1&&MONTH(MAX('Sales'[Date]))=_selectedmonth,1,0),
    IF(YEAR(MAX('Sales'[Date]))>=_maxyear-1&&YEAR(MAX('Sales'[Date]))<=_maxyear&&MONTH(MAX('Sales'[Date]))=_selectedmonth,1,0))

2. Place Measure[Flag] in Filters, set is = 1, and Apply filter.

vyangliumsft_1-1624584659207.png

3. Result:

vyangliumsft_2-1624584659209.jpeg

 

Best Regards,

Liu Yang

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

View solution in original post

6 REPLIES 6
v-yangliu-msft
Community Support
Community Support

Hi  @PwrBI01 ,

I create some data:

vyangliumsft_0-1624584659204.png

Here are the steps you can follow:

1. Create measure.

Flag =
var _selectedmonth=SELECTEDVALUE('Calendar table'[Month number])
var _maxyear=YEAR(MAXX(ALL(Sales),[Date]))
var _data=SUMX(FILTER(ALL(Sales),YEAR([Date])=_maxyear&&MONTH([Date])=_selectedmonth),[Sales])
return
IF(
    _data=BLANK(),
    IF(YEAR(MAX('Sales'[Date]))>=_maxyear-2&&YEAR(MAX('Sales'[Date]))<=_maxyear-1&&MONTH(MAX('Sales'[Date]))=_selectedmonth,1,0),
    IF(YEAR(MAX('Sales'[Date]))>=_maxyear-1&&YEAR(MAX('Sales'[Date]))<=_maxyear&&MONTH(MAX('Sales'[Date]))=_selectedmonth,1,0))

2. Place Measure[Flag] in Filters, set is = 1, and Apply filter.

vyangliumsft_1-1624584659207.png

3. Result:

vyangliumsft_2-1624584659209.jpeg

 

Best Regards,

Liu Yang

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

View solution in original post

Hi @v-yangliu-msft,

 

That is exactly what I need. Thank you very much.

 

Regards.

sanman
Helper I
Helper I

Perhaps create a calendar table with only last 2 years and create relationship with your data table

 

Calendar =
VAR getyear = YEAR(TODAY())
Var lastyear = getyear - 2
return
CALENDAR(DATE(lastyear,1,1), DATE(getyear,12,31))
 
 

Hi @sanman, thank you for your answer.

 

My situation is a little bit weird, I want that:

 

1. If the selected month is included in the last year calendar (for example I have data until May 2021 and I select a month from January to May), the chart should show the Year-To-Date sales from January 2021 to the selected month of 2021 and the Year-To-Date sales of the same period of 2020).

 

2. If the selected month is not included in the last year calendar (for example if I have data until May 2021 and I select a month from June to December), the chart should show the Year-To-Date Sales from January 2020 to the selected month of 2020 and the same period for 2019.

 

I think there is not possible to create a calendar table based on a selected value and on an IF function (I tried it and I received an error message)

 

Regards.

amitchandak
Super User
Super User

@PwrBI01 , Based on what I got, usually when you select/filter a smaller duration and want to show a larger duration you need to use an independent date table

 

refer

Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Hi @amitchandak, thank you for your answer.

 

My situation is a little bit weird, I want that:

 

1. If the selected month is included in the last year calendar (for example I have data until May 2021 and I select a month from January to May), the chart should show the Year-To-Date sales from January 2021 to the selected month of 2021 and the Year-To-Date sales of the same period of 2020).

 

2. If the selected month is not included in the last year calendar (for example if I have data until May 2021 and I select a month from June to December), the chart should show the Year-To-Date Sales from January 2020 to the selected month of 2020 and the same period for 2019.

 

I have tried to do it with an independent data table but I couldn't do it.

 

Regards.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!