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

VERY URGENT-How to get last 13 weeks excluding last week

Hi - Currently i am currently calcualting WOS = (Latest week Shipment)/(13Week avgshipments) my week is between Monday to Sunday,, if i refresh the file on Wednesday the latest week shipment will be incomplete. so i have choose the previous completed week to do the calcualtion.

 

Can someone help me??? VERY URGENT

5 REPLIES 5
Super User
Super User

Re: VERY URGENT-How to get last 13 weeks excluding last week

Hi @mohan_g_das1986 ,

 

Can you share more information about your model and expected result?

 

Please see this post regarding How to Get Your Question Answered Quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 (courtesy of @Greg_Deckler).

 

Regards

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




kcantor Super Contributor
Super Contributor

Re: VERY URGENT-How to get last 13 weeks excluding last week

@mohan_g_das1986 

For a more complete answer, please provide sample data and/or screenshots of the data and your model.

Quick down and dirty method: create a calculated column in your date table for all weeks showing whether the week number is less than the current/incomplete week. This column will change every time the data is refreshed and use it as a flag to exclude dates in or after the current week number.

Is Weeknum less =
Var Thisweek = WEEKNUM(TODAY(),2)
Return
IF([WeekNumber]<Thisweek, 1, 0)

Calculate([WOS], [Is Weeknum less] = 1)



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




mohan_g_das1986 Regular Visitor
Regular Visitor

Re: VERY URGENT-How to get last 13 weeks excluding last week

This is my
WoS_ST_new = DIVIDE([latest_week_Inventory],[13WeekAvgST_units])
latest_week_Inventory = CALCULATE([Total Inventory_Units],TOPN(1,Query1,Query1[Week]))
 
FY2019W01 to FY2019W37 (latest week),..
W37 is not complete week, now have to calculate the latest week inventory for W36? instead W7

How should i do it
mohan_g_das1986 Regular Visitor
Regular Visitor

Re: VERY URGENT-How to get last 13 weeks excluding last week

WoS_ST_new = DIVIDE([latest_week_Inventory],[13WeekAvgST_units])
latest_week_Inventory = CALCULATE([Total Inventory_Units],TOPN(1,Query1,Query1[Week]))
 
FY2019W01 to FY2019W37 (latest week),..
W37 is not complete week, now have to calculate the latest week inventory for W36? instead W7

How should i do it
kcantor Super Contributor
Super Contributor

Re: VERY URGENT-How to get last 13 weeks excluding last week

@mohan_g_das1986 

Without sample data, this is still just guessing. Please provide sample data and/or screenshots if available. Try this. Keep your latest_week_Inventory measure, add my LatestWeekInventory measure, and create the NewWoS_ST_new measure. don't remove your existing measures, just add my two versions and see if the NewWoS_ST_new works for you.

 

Add these two:

LatestWeekInventory = CALCULATE([latest_week-Inventory], [Is Weeknum less0=1)

NEWWoS_ST_new = DIVIDE([LatestWeekInventory],[13WeekAvgST_units])

 
Your existing measure:
WoS_ST_new = DIVIDE([latest_week_Inventory],[13WeekAvgST_units])
latest_week_Inventory = CALCULATE([Total Inventory_Units],TOPN(1,Query1,Query1[Week]))
 
 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 346 members 3,694 guests
Please welcome our newest community members: