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
MP-iCONN
Resolver I
Resolver I

Show previous week in visual card

Not sure why I can't figure this one out as it seems very simple but I just want to have a visual card at the top of my page that will show the last weeks dates.  I have a table that is filtered by previous week dates via a sql query and I don't actually have any date fields in my data model for the data itself.  I do however have a DateTable setup with Start of week and End of Week dates.  Our week starts on a Monday and ends on Sunday.

 

Thank you for any help or advice you can give.

 

1 ACCEPTED SOLUTION

@MP-iCONN , A measure like

=

var _st = today() +-1*WEEKDAY(today() ,2)+1 -7
var _end = today() + 7-1*WEEKDAY(today(),2) -7

return  _st & " to  " & _end

 

Use format to format the date in the required format

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@MP-iCONN , You should create a week/date table with YearWeek (YYYYWW) , Then you can create week rank in week/date table and try measure like

 

new column

Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

 

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

 

 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Lets say for example I just have a DateTable and no other Data.  My actual data is already filtered and showing everything I need in the table.  I however do not have any dates in that Data table.  I just want to show text in a card that represents the dates 02/07/22 - 02/13/22.  I am just trying to show in this report what dates are in last weeks Date Range so they know exactly what my data represents.

For Example:

MPiCONN_0-1645102132577.png

 

 

 

@MP-iCONN , A measure like

=

var _st = today() +-1*WEEKDAY(today() ,2)+1 -7
var _end = today() + 7-1*WEEKDAY(today(),2) -7

return  _st & " to  " & _end

 

Use format to format the date in the required format

That worked perfect, thank you.

 

My final measure was:

 

Date Format =
var _st = today() +-1*WEEKDAY(today() ,2)+1 -7
var _end = today() + 7-1*WEEKDAY(today(),2) -7
return FORMAT(_st , "MMM DD, YYYY") & " TO " & FORMAT(_end , "MMM DD, YYYY"
)
MPiCONN_0-1645104341685.png

 

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.