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
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.

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

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
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.