cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JMSATIRO Frequent Visitor
Frequent Visitor

sum last 4 weeks

Hi guys,

 

I have been trying calculate last 4 weeks' sum with no great results. 

 

IF i use, this will only get the last 4weeks of 2018:

Calculate(sum(BP_SELL_out[Cantidad]);Filter(BP_Sell_out; BP_Sell_out[week]<=MAX(BP_Sell_out[week]) && BP_Sell_out[week]>=MAX(BP_Sell_out[week])-3))

 

IF i use date table the same thing, an this will add a week53.

 

May data:

BP_tables.JPGI have created a week table with a weekID but none of that seems to work (yyyyww), the closest i get was filtering week and result was correct but i had to filter without that correct values didn't appear.

 

 

can you help pls.thank you.

3 REPLIES 3
Highlighted
dearwatson Member
Member

Re: sum last 4 weeks

Hi JMSATIRO

 

The problem is that the MAX Week number is always going to be 52 (the last week of the year) so this is not the "current week" as such

 

The basic approach is first getting the current date then filter the calendar to show only the period we want relative to the current date.

 

Looking at your model we need something to depict the current date or week that can be sorted into order (ideally this would be a "week ending" date) otherwise you might need to create a new Year.Week surrogate key e.g 2019.01, 2019.02...2019.12 etc. then you use that for your MAX calc.

 

So then you can use the Year.Week to get the weeks you want.

 

Cantidad Last 4 weeks = VAR CurrentWeek = MAX(BP_SELL_out[Year.Week]) RETURN CALCULATE(SUM(BP_SELL_out[Cantidad]), BP_Sell_out[Year.Week]>=CurrentWeek-0.04))

 

(you can omit the && if there are no future dates in BP_Sell_out)

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte
JMSATIRO Frequent Visitor
Frequent Visitor

Re: sum last 4 weeks

Thank you for quick response but i can't slice the data, it recognized 2018 and 2019 weeks separately though.

when i slice it doesn't sum the 4 last weeks of that date.

Community Support Team
Community Support Team

Re: sum last 4 weeks

Hi @JMSATIRO ,

 

Would you mind sharing us your dummy pbix? that would be easier for us to find the point.

 

Please upload it to the Onedrive/sharepoint, make sure you've removed the confidential info before.

 

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 281 members 3,390 guests
Please welcome our newest community members: