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

Calculating Previous without Contiguous date column but having start date and end date of week

Hello,

 

I need help with the previous week's calculation. 

 

I have the data consists of the start date and end date of the week and fiscal week column. I didn't have any date column apart from the start date and end date. I want to calculate the previous week which works in different granularity levels.

Please check the below screenshot.

 

I have tried with calander table week number also but it didn't worked and I have used relationship between two table is both side.

This the present calculation I have used.

measure option1:
PrevWeekInvQty = CALCULATE(sum(Sheet1[ Inventory Qty]),FILTER(ALL(Sheet1),MAX(Sheet1[weeknum])=Sheet1[weeknum]+1))
measure option2:
PrevWeekInvQty = var currwk =SELECTEDVALUE('Calendar'[WeekSequenceNum])
var maxwk=CALCULATE(max('Calendar'[WeekSequenceNum]),ALL('Calendar'))
return
sumx(FILTER(ALL('Calendar'),
if(currwk=1,
'Calendar'[WeekSequenceNum]=maxwk,
'Calendar'[WeekSequenceNum]=currwk-1)),
[Total Invqty])
 
Option is working only for high level granularity only.
 
 
 

Pre.PNG

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@tulasi_pbi1988 , Check how I have use week rank to deal with week

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

 

Date = CALENDAR(Date(2018,01,01),TODAY())
Mark as Date Table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date] & " to "& [Week End date]
Weekday = WEEKDAY([Date],2)

 

And use week in Rank

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last 12 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-12 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
Next 3 period Sales =
Var _min = maxx(allselected('Date','Date'[period Rank])
Var _max = maxx(allselected('Date','Date'[period Rank]) +3
CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=_min && 'Date'[Week Rank]<=_max))

 

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@tulasi_pbi1988 , Check how I have use week rank to deal with week

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

 

Date = CALENDAR(Date(2018,01,01),TODAY())
Mark as Date Table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date] & " to "& [Week End date]
Weekday = WEEKDAY([Date],2)

 

And use week in Rank

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last 12 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-12 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
Next 3 period Sales =
Var _min = maxx(allselected('Date','Date'[period Rank])
Var _max = maxx(allselected('Date','Date'[period Rank]) +3
CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=_min && 'Date'[Week Rank]<=_max))

 

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.

Hi Amit,

 

Thank you for your response.

But, here in my data there is no Sales Date as you mentioned in your example.

 

I have only Start Date and End date. So I am not able to make a relationship with the calendar table using sales date and Date.

Hi @tulasi_pbi1988 ,

 

Would you please inform us more detailed information( your  data(by OneDrive for Business)) if possible? Then we will help you more correctly.

 

Please do mask sensitive data before uploading.

 

Thanks for your understanding and support.

 

Best Regards,

Dedmon Dai

AllisonKennedy
Super User
Super User

Sorry, you screenshot doesn't make clear - which option is working and at what granularity are you missing?

 

See if this might help: https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/

 

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hi Allison,

 

Sorry...

Measure Option1 is working.

For that I have created week Number in Fact table only.

 

Thank you

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.