Last "n" days/weeks based on selected values

[Anonymous]

Dear All, 

I got a fact and date dimension linked each other, objective is to get the last "n" weeks based on select week from slicer.

Suppose says selected week is W14-19, and want to see trend of last 8 weeks,  then sales should show from W07-19 to W14-16

Would it possible to have this achived, pls do suggest.

Thanks

[v-cherch-msft]

Hi @Anonymous 

You need a calendar table with a unique Week ID column. For further:

http://exceleratorbi.com.au/power-pivot-calendar-tables/

As as long as you have a unique week ID column, you can write a formula like below:

Last 8 Weeks =
CALCULATE (
    [Total Sales],
    FILTER (
        ALL ( Calendar ),
        Calendar[WeekID]
            >= MAX ( Calendar[WeekID] ) - 7
            && Calendar[WeekID] <= MAX ( Calendar[WeekID] )
    )
)

Here is the post about last n months for your reference.

Regards,

[Anonymous]

Thanks @v-cherch-msft , this does gives last N weeks sales, but the it aggregates against the selected values and cant group based on weeks, however this post helped, but the DATESINPERIOD doesnt accept Week, instead of week have tried for DAY by converting weeks into days, but the problem is at year end, trying to figure this out.

[v-cherch-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution and welcome to share your own solution. More people will benefit from here. If not, please share more details for us so that we could help further on it.

Regards,

[v-cherch-msft]

Hi @Anonymous 

Could you share some sample data?Sample data and expected output will be helpful to provide an accurate solution.

It would be better if you can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

Regards,