Last 8 dates/weeks of data

[Lamdasquare]

Hello Forum!  

Newbie here, been spinning my head on this for a few days so thought i'd post here.  Trying to pull the last 8 dates/weeks from my dataset.  I'm not sure if I have to create a date table to do this or if some form of calculate will make it work - i've been trying a few calculate statements, datesinperiod but it seems to pull all 9 values that I currently have instead of just the 8 I think it should so my forumla seems off.  

What my table has is a date column, sales, geo, and category.  The date is not daily but a weekly date - so it gives every 7th day date - from Monday I believe so the dates would look like, for example, Jan 1, Jan 8, Jan 15, Jan 22.... etc.  

I'm trying to put together a visual that will show the last 8 dates/weeks of data broken out by category and this will change obviously from week to week as a new weeks worth of data comes in once its refreshed so it has to be rolling.  Is it possible to do some sort of calc on the date field to just do a -8 and get it to work?  I even thought of using a weeknum column but I wasn't able to get that to work either.

Thanks for any responses/help!!

[Anonymous]

Hi @Lamdasquare 

@ryan_mayu Thank you very much for your prompt reply. Please allow me to share some other methods here.

Create a measure to dynamically sort dates. And look for the top eight data.

Last 8 weeks Sales = 
var _rank = RANKX(ALL('Table'), CALCULATE(MAX('Table'[Date])),, DESC, Dense)
RETURN
IF(
    _rank <= 8,
    SELECTEDVALUE('Table'[Sales]),
    BLANK()
)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

could you pls provide some sample data and expected output?

[Lamdasquare]

Sure - here is a sample table:  We will assume for this table the date is the week after 3/11 so 3/11 is the last updated value.  The first few give you an idea of the data - for simplicity sake I just used the next weeks dates to show you a table of 11 weeks of data and I am looking to pull the last 8 weeks of data from 3/11 backwards.  

Date	Geo	Category	Sales
1/1/2024	US	1	4
1/1/2024	Canada	1	5.3
1/1/2024	England	1	2
1/1/2024	France	1	1
1/1/2024	US	2	2
1/1/2024	Canada	2	3.4
1/1/2024	England	2	6
1/1/2024	France	2	9
1/7/2024	US	3	1
1/7/2024	Canada	3	1
1/7/2024	England	3	2
1/7/2024	France	3	3
1/14/2024	US	1	4
1/14/2024	Canada	1	5.3
1/14/2024	England	1	2
1/14/2024	France	1	1
1/21/2024	US	2	2
1/28/2024	Canada	2	3.4
2/4/2024	England	2	6
2/11/2024	France	2	9
2/18/2024	US	3	1
2/25/2024	Canada	3	1
3/4/2024	England	3	2
3/11/2024	France	3	3

So I would expect to pull all data from 3/11 back to 1/21 as that is 8 weeks worth of data.  So then when I put it into a line chart visual it will have the categories going up/down in zig zaggy lines by Geo with the 8 dates showing on the bottom to show how each category is changing by week.  

[ryan_mayu · Accepted solution]

you can create a column

Column = if('Table'[Date]>=(max('Table'[Date])-56),"y")

then you add the column to the visual filter and select y

pls see the attachment below

[Lamdasquare]

Thanks Ryan - that worked although with a funny side effect.  I see what you were doing there with the -56 as 8x7 but it would return 9 weeks of data instead of 8 so I had to lower it to 49 days instead of 56.  
v-nuoc - for some reason when I created your measure and then put it into my visual - it errors out eventhough the code is a match.  

[ryan_mayu]

you are welcome