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,
I am creating a dashboard which comparision current week sales vs Last week sales vs Last month Sales based on the date slicer selection using the below table.
5/1/2022 | 65 |
5/2/2022 | 75 |
5/3/2022 | 85 |
5/4/2022 | 95 |
5/5/2022 | 105 |
5/6/2022 | 115 |
5/7/2022 | 125 |
5/8/2022 | 135 |
5/9/2022 | 145 |
5/10/2022 | 155 |
5/11/2022 | 165 |
5/12/2022 | 175 |
5/13/2022 | 185 |
5/14/2022 | 195 |
5/15/2022 | 205 |
5/16/2022 | 215 |
5/17/2022 | 225 |
5/18/2022 | 235 |
5/19/2022 | 245 |
5/20/2022 | 255 |
5/21/2022 | 265 |
5/22/2022 | 275 |
5/23/2022 | 285 |
5/24/2022 | 295 |
5/25/2022 | 305 |
5/26/2022 | 315 |
5/27/2022 | 325 |
5/28/2022 | 335 |
5/29/2022 | 345 |
5/30/2022 | 355 |
5/31/2022 | 365 |
6/1/2022 | 375 |
6/2/2022 | 385 |
6/3/2022 | 395 |
6/4/2022 | 405 |
6/5/2022 | 415 |
6/6/2022 | 425 |
6/7/2022 | 435 |
6/8/2022 | 445 |
6/9/2022 | 455 |
6/10/2022 | 465 |
6/11/2022 | 475 |
6/12/2022 | 485 |
6/13/2022 | 495 |
6/14/2022 | 505 |
6/15/2022 | 515 |
6/16/2022 | 525 |
6/17/2022 | 535 |
6/18/2022 | 545 |
6/19/2022 | 555 |
6/20/2022 | 565 |
6/21/2022 | 575 |
6/22/2022 | 585 |
6/23/2022 | 595 |
6/24/2022 | 605 |
6/25/2022 | 615 |
6/26/2022 | 625 |
6/27/2022 | 635 |
6/28/2022 | 645 |
6/29/2022 | 655 |
6/30/2022 | 665 |
7/1/2022 | 675 |
7/2/2022 | 685 |
7/3/2022 | 695 |
7/3/2022 | 705 |
7/2/2022 | 715 |
7/1/2022 | 725 |
7/3/2022 | 735 |
7/2/2022 | 745 |
7/1/2022 | 755 |
7/1/2022 | 765 |
7/3/2022 | 775 |
7/2/2022 | 785 |
7/1/2022 | 795 |
7/1/2022 | 805 |
7/2/2022 | 815 |
7/3/2022 | 825 |
7/2/2022 | 835 |
7/1/2022 | 845 |
7/3/2022 | 855 |
7/2/2022 | 865 |
7/3/2022 | 875 |
7/2/2022 | 885 |
7/2/2022 | 895 |
7/1/2022 | 905 |
7/2/2022 | 915 |
7/1/2022 | 925 |
7/1/2022 | 935 |
7/2/2022 | 945 |
7/3/2022 | 955 |
7/1/2022 | 965 |
7/3/2022 | 975 |
7/3/2022 | 985 |
7/2/2022 | 995 |
7/3/2022 | 1005 |
7/4/2022 | 1015 |
7/5/2022 | 1025 |
7/6/2022 | 1035 |
7/7/2022 | 1045 |
7/8/2022 | 1055 |
7/9/2022 | 1065 |
7/10/2022 | 1075 |
7/11/2022 | 1085 |
7/12/2022 | 1095 |
7/13/2022 | 1105 |
7/14/2022 | 1115 |
7/15/2022 | 1125 |
7/16/2022 | 1135 |
7/17/2022 | 1145 |
7/18/2022 | 1155 |
7/19/2022 | 1165 |
7/20/2022 | 1175 |
7/21/2022 | 1185 |
7/22/2022 | 1195 |
7/23/2022 | 1205 |
7/24/2022 | 1215 |
7/25/2022 | 1225 |
7/26/2022 | 1235 |
7/27/2022 | 1245 |
7/28/2022 | 1255 |
7/29/2022 | 1265 |
7/30/2022 | 1275 |
7/31/2022 | 1285 |
Below measures created in the dashboard.
Solved! Go to Solution.
Hi @baijumohan1990 ,
If you want to compare the sum of the current week with the sum of the previous week. You can create a WeekNum column to return the week numbers.
WeekNum = WEEKNUM([Date],2)
Then create a column to get the sum of previous week.
PreviousWeekValue = CALCULATE(SUM('Table'[Value]),FILTER('Table',[WeekNum]=EARLIER('Table'[WeekNum])-1))
Also the current week.
CurrentWeekValue = CALCULATE(SUM('Table'[Value]),FILTER('Table',[WeekNum]=EARLIER('Table'[WeekNum])))
If you want to compare the current day with the date corresponding to the previous week.
You can directly filter the date for the day minus 7.
Column = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Date]=EARLIER('Table'[Date])-7))
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @baijumohan1990 ,
If you want to compare the sum of the current week with the sum of the previous week. You can create a WeekNum column to return the week numbers.
WeekNum = WEEKNUM([Date],2)
Then create a column to get the sum of previous week.
PreviousWeekValue = CALCULATE(SUM('Table'[Value]),FILTER('Table',[WeekNum]=EARLIER('Table'[WeekNum])-1))
Also the current week.
CurrentWeekValue = CALCULATE(SUM('Table'[Value]),FILTER('Table',[WeekNum]=EARLIER('Table'[WeekNum])))
If you want to compare the current day with the date corresponding to the previous week.
You can directly filter the date for the day minus 7.
Column = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Date]=EARLIER('Table'[Date])-7))
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thank you. it worked. I tried another solution with DATEADD that also worked. Now I confused why a fairly simple query not working when we compute the Last Week Sales with DATESBETWEEN. is DATESBETWEEN meant be for any other scenarios? Appreciate your inputs on this. thank you
Hi @baijumohan1990 ,
If your problem has been solved, could you please mark the reply as a solution?
More people will benefit from it.😁
Best Regards,
Stephen Tao
Hi @baijumohan1990 ,
The official documentation explains:
The DATEADD function returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current context.
While the DATESBETWEEN function returns a table that contains a column of dates that begins with a specified start date and continues until a specified end date.
Hope to help you.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
108 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |