Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Group Calendar Date by Last 7 Week Days Dynamically Calculate column

Hi

Need to group dates into last 7 days of my calendar table in a column, I tryied with out luck and finally llooks I´m very close with this calculated column:

First Day 7 Last Days =
VAR today = today()
return
CALCULATE(
max('Calendar Creation'[WeekStart]);DATESINPERIOD('Calendar Creation'[Date];today; -7;DAY))
Of course it's not the desired result, the desired result will be like :
 
 

This dataset it´s refreshed 4 times a day, it´s need to be  grouped  it dynamically in every refresh on this pattern , sorry but i´m lost on this.

Thanks in advance

Need Help
Tips and Tricks

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    It didn´t allow me to paste the screenshot.... here´s the table desired 😉

    DateWeekStartFirst Day 7 Last DaysDesired Result
    13/04/2020 0:0013/04/2020 0:0013/04/2020 0:0013/04/2020 0:00
    12/04/2020 0:0006/04/2020 0:0013/04/2020 0:0013/04/2020 0:00
    11/04/2020 0:0006/04/2020 0:0013/04/2020 0:0013/04/2020 0:00
    10/04/2020 0:0006/04/2020 0:0013/04/2020 0:0013/04/2020 0:00
    09/04/2020 0:0006/04/2020 0:0013/04/2020 0:0013/04/2020 0:00
    08/04/2020 0:0006/04/2020 0:0013/04/2020 0:0013/04/2020 0:00
    07/04/2020 0:0006/04/2020 0:0013/04/2020 0:0013/04/2020 0:00
    06/04/2020 0:0006/04/2020 0:0013/04/2020 0:0006/04/2020 0:00
    05/04/2020 0:0030/03/2020 0:0013/04/2020 0:0006/04/2020 0:00
    04/04/2020 0:0030/03/2020 0:0013/04/2020 0:0006/04/2020 0:00
    03/04/2020 0:0030/03/2020 0:0013/04/2020 0:0006/04/2020 0:00
    02/04/2020 0:0030/03/2020 0:0013/04/2020 0:0006/04/2020 0:00
    01/04/2020 0:0030/03/2020 0:0013/04/2020 0:0006/04/2020 0:00
    31/03/2020 0:0030/03/2020 0:0013/04/2020 0:0006/04/2020 0:00
    30/03/2020 0:0030/03/2020 0:0013/04/2020 0:0030/03/2020 0:00
    29/03/2020 0:0023/03/2020 0:0013/04/2020 0:0030/03/2020 0:00
    28/03/2020 0:0023/03/2020 0:0013/04/2020 0:0030/03/2020 0:00
    27/03/2020 0:0023/03/2020 0:0013/04/2020 0:0030/03/2020 0:00
    26/03/2020 0:0023/03/2020 0:0013/04/2020 0:0030/03/2020 0:00
    25/03/2020 0:0023/03/2020 0:0013/04/2020 0:0030/03/2020 0:00
    24/03/2020 0:0023/03/2020 0:0013/04/2020 0:0030/03/2020 0:00
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous 

    Not sure I got it, but you can get something like that

    Rolling 7 = CALCULATE(Max(Sales[Sales Date]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-7,Day))

     

    Where sales is my transaction table

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Sorry, nice idea but I tryied and does not work, I need a separate column in calendar table to use it in a chart to group data by this column.

      I need it to create it whit out relation to a transaction table. 

      This week start day needs to change every 7 days. Example for this week it´s 13-4-2020 for the last 7 days, after 7 days should be  the first day of the second group of 7 days,  etc...

      This has to change everyday, your solution places the max value of transaction in all column 😞

      Thanks anyway for your time

       

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago
    So, I would think:

    Last 7 Days =
    VAR __today = TODAY()
    RETURN
    IF([Date] >= (__today - 7),"Y","N")
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Anonymous how to  itterate the last 7 days loggic till last 365 days ??