cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Getting data for last week Monday - Sunday in Powerbi

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

1 ACCEPTED SOLUTION
Super User

@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))

11 REPLIES 11
Anonymous
Not applicable

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
Not applicable

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
Not applicable

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
Not applicable

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
Not applicable

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
Not applicable

Sorry, i mean Amit

Super User

@Anonymous , refer my wow blog for that

Anonymous
Not applicable

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

Super User

@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
Not applicable

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

Super User

@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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors