Forum Discussion

baijumohan1990's avatar
baijumohan1990
Helper II
4 years ago
Solved

Current Week Vs Previous Week values are not showing correctly.

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/202265
5/2/202275
5/3/202285
5/4/202295
5/5/2022105
5/6/2022115
5/7/2022125
5/8/2022135
5/9/2022145
5/10/2022155
5/11/2022165
5/12/2022175
5/13/2022185
5/14/2022195
5/15/2022205
5/16/2022215
5/17/2022225
5/18/2022235
5/19/2022245
5/20/2022255
5/21/2022265
5/22/2022275
5/23/2022285
5/24/2022295
5/25/2022305
5/26/2022315
5/27/2022325
5/28/2022335
5/29/2022345
5/30/2022355
5/31/2022365
6/1/2022375
6/2/2022385
6/3/2022395
6/4/2022405
6/5/2022415
6/6/2022425
6/7/2022435
6/8/2022445
6/9/2022455
6/10/2022465
6/11/2022475
6/12/2022485
6/13/2022495
6/14/2022505
6/15/2022515
6/16/2022525
6/17/2022535
6/18/2022545
6/19/2022555
6/20/2022565
6/21/2022575
6/22/2022585
6/23/2022595
6/24/2022605
6/25/2022615
6/26/2022625
6/27/2022635
6/28/2022645
6/29/2022655
6/30/2022665
7/1/2022675
7/2/2022685
7/3/2022695
7/3/2022705
7/2/2022715
7/1/2022725
7/3/2022735
7/2/2022745
7/1/2022755
7/1/2022765
7/3/2022775
7/2/2022785
7/1/2022795
7/1/2022805
7/2/2022815
7/3/2022825
7/2/2022835
7/1/2022845
7/3/2022855
7/2/2022865
7/3/2022875
7/2/2022885
7/2/2022895
7/1/2022905
7/2/2022915
7/1/2022925
7/1/2022935
7/2/2022945
7/3/2022955
7/1/2022965
7/3/2022975
7/3/2022985
7/2/2022995
7/3/20221005
7/4/20221015
7/5/20221025
7/6/20221035
7/7/20221045
7/8/20221055
7/9/20221065
7/10/20221075
7/11/20221085
7/12/20221095
7/13/20221105
7/14/20221115
7/15/20221125
7/16/20221135
7/17/20221145
7/18/20221155
7/19/20221165
7/20/20221175
7/21/20221185
7/22/20221195
7/23/20221205
7/24/20221215
7/25/20221225
7/26/20221235
7/27/20221245
7/28/20221255
7/29/20221265
7/30/20221275
7/31/20221285

 

 

 

 

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? 
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      baijumohan1990
      Helper II
      4 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.