Last Week of selected Date range

[Uzi2019]

Hi Expert,
I have one Date Range Slicer and As per the range selection it will calculate the Sale of the entire week data of that selected range.

For Example,

If User Selects End Range 4th, Aug 2022 so it will return Sales value of 1-4 Aug. 

Week should be considered as Monday to Sunday.

Please help me to calculate this using DAX.

[Tahreem24 · Accepted solution]

Hi @Uzi2019 ,Try this:

Create Week No Column:

Week No = WEEKNUM('TableName'[DateColumn],2)

Then create another column for Week Ranking:

Week Rank = RANKX(ALL('TableName'),'TableName'[Week No],,ASC)

Then last step is to create a measure like below:

Current Week =
VAR latestWeek = CALCULATE(MAX('TableName'[Week Rank]),ALLSELECTED('TableName'))
VAR Result_ = CALCULATE(AVERAGE('TableName'[Sales]),FILTER('TableName','TableName'[Week Rank]=latestWeek))
RETURN Result_

[Uzi2019]

Hi @Tahreem24 ,
Thanks alot. Your solution works for me.

[amitchandak]

@Uzi2019 , Create a date table with following columns

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)

Then measures like

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

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

This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))

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

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Uzi2019]

Hi @amitchandak 
I tried your solution it did not work.Do yu have any other way of doing it?