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/2022 65 5/2/2022 ...
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.

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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