Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mohan_g_das1986
Helper III
Helper III

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
kcantor
Community Champion
Community Champion

@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 Super User!




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 

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 Super User!




MFelix
Super User
Super User

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.