Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pbi_newuser2023
Frequent Visitor

How to view last year full year trend and this year trend in line chart

Hi, users would like to view the trend of current year and previous year when the month is selected. For example, when users select Month = March, they are still able to see the data from Jan to Dec for previous year and from Jan to Mar for current year. Is that possible? Thanks.

 

pbi_newuser2023_0-1686010539532.png

 

 

DateSales QtyDelivered Qty% Delivered
1/1/2021         289                   3713%
1/2/2021           83                   4757%
1/3/2021         412                   7117%
1/4/2021         281                   4416%
1/5/2021         165                   2213%
1/6/2021         648                   223%
1/7/2021         221                   6329%
1/8/2021         474                   6514%
1/9/2021         660                   132%
1/10/2021         295                   7726%
1/11/2021           68                      69%
1/12/2021           99                   1717%
1/1/2022         979                   929%
1/2/2022         602                   498%
1/3/2022         300                   4816%
1/4/2022         198                   8543%
1/5/2022         555                   7513%
1/6/2022           72                   6387%
1/7/2022         835                   9511%
1/8/2022         112                   4944%
1/9/2022         791                   7710%
1/10/2022         453                   4911%
1/11/2022         647                   7812%
1/12/2022         350                   5616%
1/1/2023         886                 70079%
1/2/2023         337                   3811%
1/3/2023         779                   9512%
1/4/2023           42                   3072%
 

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@pbi_newuser2023 , if you select a value and want to show more than that, then the slicer needs to be on an independent date table

 

//Date1 is independent Date table, Date is joined with Table
This Year =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -1*month(_max)) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

 

 

Last Year =
var _max1 = maxx(allselected(Date1),Date1[Date])

var _max = eomonth(_max1, -1*month(_max1))
var _min = eomonth(_max, -1*month(_max)) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

 

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@pbi_newuser2023 , if you select a value and want to show more than that, then the slicer needs to be on an independent date table

 

//Date1 is independent Date table, Date is joined with Table
This Year =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -1*month(_max)) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

 

 

Last Year =
var _max1 = maxx(allselected(Date1),Date1[Date])

var _max = eomonth(_max1, -1*month(_max1))
var _min = eomonth(_max, -1*month(_max)) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

 

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

Hi @amitchandak , I tried to use the suggested solution but got the below error message. Is there any workaround?

pbi_newuser2023_0-1686021029625.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.