Current Week Vs Previous Week values are not showing correctly.

[baijumohan1990]

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. 

StartDate = CALCULATE(MIN('Date'[Date]), ALLSELECTED('Date'[Date]))

EndDate = CALCULATE(MAX('Date'[Date]), ALLSELECTED('Date'[Date]))

DIFF = DATEDIFF( [EndDate] , [StartDate],DAY)-1

LastWkStart = CALCULATE([StartDate],DATEADD('Date'[Date],Data[DIFF],DAY))

LastWkEnd = CALCULATE([StartDate],DATEADD('Date'[Date],-1,DAY))

LastMonthStart = CALCULATE([StartDate],DATEADD('Date'[Date],-1,MONTH))

LastMonthEnd = CALCULATE([EndDate],DATEADD('Date'[Date],-1,MONTH))

Notificaction_Count = sum(Data[cnt_mov])

NotificationCountLM = CALCULATE(sum(Data[cnt_mov]),DATESBETWEEN('Date'[Date],[LastMonthStart],[LastMonthEnd]))

NotificationCountLWk = CALCULATE(sum(Data[cnt_mov]),DATESBETWEEN('Date'[Date],[LastWkStart],[LastWkEnd]))

The dashboard is showing the current week & previous month values correctly. But, Previous week values are not correct. 

 Any reason for this? 

[v-stephen-msft · Accepted 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.

[baijumohan1990]

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

[v-stephen-msft]

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

[v-stephen-msft]

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.