Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Date | Sales Qty | Delivered Qty | % Delivered |
1/1/2021 | 289 | 37 | 13% |
1/2/2021 | 83 | 47 | 57% |
1/3/2021 | 412 | 71 | 17% |
1/4/2021 | 281 | 44 | 16% |
1/5/2021 | 165 | 22 | 13% |
1/6/2021 | 648 | 22 | 3% |
1/7/2021 | 221 | 63 | 29% |
1/8/2021 | 474 | 65 | 14% |
1/9/2021 | 660 | 13 | 2% |
1/10/2021 | 295 | 77 | 26% |
1/11/2021 | 68 | 6 | 9% |
1/12/2021 | 99 | 17 | 17% |
1/1/2022 | 979 | 92 | 9% |
1/2/2022 | 602 | 49 | 8% |
1/3/2022 | 300 | 48 | 16% |
1/4/2022 | 198 | 85 | 43% |
1/5/2022 | 555 | 75 | 13% |
1/6/2022 | 72 | 63 | 87% |
1/7/2022 | 835 | 95 | 11% |
1/8/2022 | 112 | 49 | 44% |
1/9/2022 | 791 | 77 | 10% |
1/10/2022 | 453 | 49 | 11% |
1/11/2022 | 647 | 78 | 12% |
1/12/2022 | 350 | 56 | 16% |
1/1/2023 | 886 | 700 | 79% |
1/2/2023 | 337 | 38 | 11% |
1/3/2023 | 779 | 95 | 12% |
1/4/2023 | 42 | 30 | 72% |
Solved! Go to Solution.
@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
@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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |