Rolling 7 Days Dates Table

[blimbert]

Hello - I am relatively new to PQ. I am trying to show a trend of activity over 7 day increments. I have built a dynamic dates table and added that to the data model. In that table I have lots of date periods such a week of year, quarter, etc. However, I do not have on that shows weeks in a consistent 7 day increment from a given date.

For example:
Today is 10/1/20, which I would like to key the "week of" column from
This "week of" column for 10/1 would display 9/25 (so that 9/25 - 10/1 = 7 days, and any date between 9/25 and 10/1 would show 9/25.
Going back to the dates table:if the date was 9/24, it would show week of 9/18, since the prior 7 day period was 9/18 - 9/24
if the date table date was 10/3/20, the week of would be 10/2/20, since the next 7 day period is 10/2 - 10/8

Thoughts? Feels like this is hard to describe/explain, but feels simple given the power of PQ functions.

 

[sandy666]

out of curiosity: why you add your table to the Data Model if you want solution in Power Query?

 

[blimbert]

Thanks for the response, it’s a fair question, the answer is I’m likely doing it wrong or misunderstanding the total functionality of one or the other.

my thought was that I put the date table and fields generated by the dates table in PQ Into the data model so that I can join them to other tables by dates and then use the various dates columns for different groupings in pivot tables?

Am I going about this inefficiently?

 

[sandy666]

I don't know what you want but with your description in post#3 you can merge tables by dates in PQ , unpivot if necessary, then group or use standard Pivot
of course you can use Data Model after prepare proper table then use any Measures if necessary and then Power Pivot
but PQ ? Data Model is a one way ticket, you can't back to PQ from Data Model

 

[sandy666]

for more complicated DAX I suggest to use DAX Studio