Latest 4 weeks number sales values

[Anonymous]

Is there a simple way to filter to the latest 4 weeks for sales in a measure but i dont have a date table and wont as i am working with week numbers so i just want to look at the sales associated with the 4 latest week numbers. I can do this via the TopN function but need it in a measure format

[v-stephen-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,
Stephen Tao

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Since you connection mode is live connection, you can create a calendar table by DAX.

For example:

Table = CALENDAR(DATE(2020,1,1),DATE(2020,12,31))

CALENDAR function (DAX) 

Then create a relationship between your main table and the new calendar table.

Put Date column in the calendar table into Filters on this visual or all pages and it should do the job.

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.

[PaulDBrown]

@Anonymous 

I recommend you set up a period table covering the range for the Year, Week, and YearWeek in your dataset. Include an index column for the YearWeek column. Create a relationship between the YearWeek column in your period table and an equivalent YearWeek column in your fact table.

You can then use this index to subtract 4 weeks in your measures.

If you can provide a sample dataset I can work on the example for you.

[Anonymous]

@PaulDBrown as this is live connection i dont have capacity to do this i am only able to create measures in the Power bi. so am looking to find a way to filter to latest 4 weeks based on dates that are weekending dates

[Anonymous]

@PaulDBrown the format i have the field in is this 03/10/2020 00:00:00