cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Last "n" days/weeks based on selected values

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

4 REPLIES 4
Highlighted
Microsoft
Microsoft

Re: Last "n" days/weeks based on selected values

Hi @Nagaharsha 

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

Re: Last "n" days/weeks based on selected values

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.

Highlighted
Microsoft
Microsoft

Re: Last "n" days/weeks based on selected values

Hi @Nagaharsha 

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Microsoft
Microsoft

Re: Last "n" days/weeks based on selected values

Hi @Nagaharsha 

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors