Help with calculation

[trwatts]

Hey everyone, 

I need to work out how many weeks there are in a month but there are some conditions. 
The end goal is to calculate the number of purchases an individual customer makes each week. 

* Need the weeks based on workdays. e.g Mon-Frid is a week. The reason I need to ignore weekends is our sites don't operate on the weekends. Thats important as if a week is between two months say changing on a Wednesday I will need the Monday and Tuesday to calculate to .4 of a week based on five days rather than 0.28 based on 7 days.

* Need to be able to calculate the number of weeks that contain a sale. 

* Need to be able to use that calculation to be filtered by a product type. 

* I would like to only calculate weeks that include a sale. 

Any help would be great.

[v-xiaotang]

Hi @trwatts 

Have you solved this question with VijayP's and amitchandak's help? If you have solved the question, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please provide more details,

(1) a sample file, you can replace raw data with bogus data to protect your privacy.

     or provide some sample data that fully covers your issue/question.

(2) give your expected result based on the sample you provide.

Kindly note: Please ensure the data in sample is concise and representative.

Thanks.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[VijayP]

@trwatts 
Use this file to see whether you are able to get the solution!

[amitchandak]

@trwatts , You create create a date table

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

WeekDay = WEEKDAY([Date],2)

WeekDay flag = if(WEEKDAY([Date],2) < 6,1,0)

then measure like

This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank] && 'Date'[WeekDay flag] =1 )))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[WeekDay flag] =1 ))

4th last week

Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-4 && 'Date'[WeekDay flag] =1 ))

Product Type should filter if joined, else we need add code