Group Calendar Date by Last 7 Week Days Dynamically Calculate column

[JuanviWebbeds]

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

[Jabillerave]

@JuanviWebbeds how to  itterate the last 7 days loggic till last 365 days ?? 

[Greg_Deckler]

So, I would think:

Last 7 Days =
VAR __today = TODAY()
RETURN
IF([Date] >= (__today - 7),"Y","N")

[JuanviWebbeds]

Hi, thanks but does not work either.

This solution evaluates today and puts and Y if it´s in the last 7 days.

I need to put there max of week start in 7 days rolling periods.

from today to today -6 --> todays date

from day 6 to 14 --> day 6 date

from day 15 to 21 --> day 15 date

etc...

Thanks in advance

[Greg_Deckler]

You might also want to review Sequential: https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231#M116

[JuanviWebbeds]

Thanks for both resources, I had a quick view and they are very interesting, but does not match to my requirementes, finally I found a solution,:

Max Week Date Last 7 Days =

VAR __date = 'Calendar Cancelled'[Date]

RETURN

CALCULATE(MAX('Calendar Cancelled'[Date]); DATESINPERIOD('Calendar Cancelled'[Date];__date;+7;day))

This groups by the top date of the current date + 7 days 🙂

Thanks all for your help and sorry for the explanation, it is easy but I was lost.

Aprreciatte your help

[Greg_Deckler · Accepted solution]

Check out Week Starting. https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Starting/m-p/391487#M123

[amitchandak]

@JuanviWebbeds 

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

[JuanviWebbeds]

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

[JuanviWebbeds]

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

Date	WeekStart	First Day 7 Last Days	Desired Result
13/04/2020 0:00	13/04/2020 0:00	13/04/2020 0:00	13/04/2020 0:00
12/04/2020 0:00	06/04/2020 0:00	13/04/2020 0:00	13/04/2020 0:00
11/04/2020 0:00	06/04/2020 0:00	13/04/2020 0:00	13/04/2020 0:00
10/04/2020 0:00	06/04/2020 0:00	13/04/2020 0:00	13/04/2020 0:00
09/04/2020 0:00	06/04/2020 0:00	13/04/2020 0:00	13/04/2020 0:00
08/04/2020 0:00	06/04/2020 0:00	13/04/2020 0:00	13/04/2020 0:00
07/04/2020 0:00	06/04/2020 0:00	13/04/2020 0:00	13/04/2020 0:00
06/04/2020 0:00	06/04/2020 0:00	13/04/2020 0:00	06/04/2020 0:00
05/04/2020 0:00	30/03/2020 0:00	13/04/2020 0:00	06/04/2020 0:00
04/04/2020 0:00	30/03/2020 0:00	13/04/2020 0:00	06/04/2020 0:00
03/04/2020 0:00	30/03/2020 0:00	13/04/2020 0:00	06/04/2020 0:00
02/04/2020 0:00	30/03/2020 0:00	13/04/2020 0:00	06/04/2020 0:00
01/04/2020 0:00	30/03/2020 0:00	13/04/2020 0:00	06/04/2020 0:00
31/03/2020 0:00	30/03/2020 0:00	13/04/2020 0:00	06/04/2020 0:00
30/03/2020 0:00	30/03/2020 0:00	13/04/2020 0:00	30/03/2020 0:00
29/03/2020 0:00	23/03/2020 0:00	13/04/2020 0:00	30/03/2020 0:00
28/03/2020 0:00	23/03/2020 0:00	13/04/2020 0:00	30/03/2020 0:00
27/03/2020 0:00	23/03/2020 0:00	13/04/2020 0:00	30/03/2020 0:00
26/03/2020 0:00	23/03/2020 0:00	13/04/2020 0:00	30/03/2020 0:00
25/03/2020 0:00	23/03/2020 0:00	13/04/2020 0:00	30/03/2020 0:00
24/03/2020 0:00	23/03/2020 0:00	13/04/2020 0:00	30/03/2020 0:00