Getting data for last week Monday - Sunday in Powerbi

[Anonymous]

Hi, I have to display count of custid's based on Purchase date for last week (monday -Sunday) and the week Previous to that (Previous monday - Sunday of last week) in matrix visual.

Can someone please suggest the best way to achieve this ?

Many Thanks

[Anonymous]

Hi Amit I finally worked this out without using the RANK. Below are my meaures.

CurrentReservations =
VAR PrevSunday = TODAY() - WEEKDAY(TODAY(),2)
VAR PrevMonday = PrevSunday - 6
RETURN CALCULATE (COUNTROWS('Sample') , FILTER(ALL('Date') , 'Date'[Date] >= PrevMonday && 'Date'[Date] <= PrevSunday))

PrevReservations =
VAR PrevPrevSunday = TODAY() - WEEKDAY(TODAY(),2) - 7
VAR PrevPrevMonday = PrevPrevSunday - 6
RETURN CALCULATE (COUNTROWS('Sample') , FILTER(ALL('Date') , 'Date'[Date] >= PrevPrevMonday && 'Date'[Date] <= PrevPrevSunday))

Many Thanks for your help and guidance so far. Much appricated Amit

[Anonymous]

Hi Amit, can you please update me your week rank according to my requirement ? my report should only display data for last week Monday-Sunday for current and the previous week Monday -Sunday data for previous.

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

Many Thanks

[Anonymous]

Amit, I think I need to create the date column first right ? can i use the below dax first to create the date column for my date table and then create the remaining 3 columns which you suggested ?

Date = CALENDAR (DATE (1901, 1, 1), DATE (2099, 12, 31))

Thanks

[Anonymous]

Hi Amit, while creating the new columns in the date table, it says date column not availabe in the date table. 

this is true right ? we dont have date column in this table as its new table right ? can you please advice ? Thanks

[Anonymous]

Hi Amit, its not allowing me to create any new columns in the new date table . its throwing error saying column Date cannot be found in table Date. this is correct as we dont have any column name date right ? please advice.

Thanks

[Anonymous]

Sorry, i mean Amit

[amitchandak]

@Anonymous , refer my wow blog for that

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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Anonymous]

Hi Arun, but when i copy paste your DAX for creating the Date table, its throwing errors. can you please advice ? Thanks

[amitchandak · Accepted solution]

@Anonymous , please do necessary modifications

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)

measures
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 year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 8 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
last two weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])-1
&& 'Date'[Week Rank]>=max('Date'[Week Rank])-3))

[Anonymous]

ok thanks Amit. So I will create 3 new columns in my new DATE table as you suggested. But where exactly do I need to create the measures ? is it good practice to create measures in a new table called Measures ?

Many Thanks

[amitchandak]

@Anonymous , measure belongs to Model. So the idle place is a measure table or the table having the base column.

refer this for measure management - https://www.youtube.com/watch?v=b9oDIL8nbu0