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
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors