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
VK
New Member

Display this week & this month data

Hello,

 

I am new to Power BI and on a graph I would like to display data for this week. For example, if the week starts on Monday and today is Monday, it will show data for Monday. If today is Tuesday, it will show Monday and Tuesday data, if it is Wednesday, it will show Monday, Tuesday, and Wednesday of this week, etc.

 

I am trying to achieve similar for "this month" data as well, so it doesn't show the last 30 days, but starts from the beginning of the month until today's date.

 

Could you please help finding the right direction? Thanks.

 

 

1 ACCEPTED SOLUTION
elliotdixon
Responsive Resident
Responsive Resident

HI @VK

This is pretty easy - one of the main reasons I like Power BI.

First thing is that you need to have another table that has just Dates in it.

Create a link between the date data in your Opportunity table and then you can create calculated colums in the dates table that will provide you with the answers.

 

1. Dates Table

Good table to start out with is http://blog.crossjoin.co.uk/2013/11/19/generating-a-date-dimension-table-in-power-query/
this will give you Date,DayOfMonth,Year,DayOfWeekNum etc.

 

Then create the following

 

DAX Measures

Today:=DATE(year(now()),MONTH(NOW()), DAY(NOW()))

DAX Calculated Columns

IsInCurrentYear

=if(YEAR(NOW())= [Year],1,0)

 

WeekOfYearNumber

=WEEKNUM([Date],2)


IsInCurrentWeek

=if([isInCurrentYear] && WEEKNUM(NOW())=[WeekOfYearNumber],1,0)

 

IsInCurrentYear = if(YEAR(NOW())= [Year],1,0)
// Column to see if it is the current year

 

IsInLastWeek

=if([isInCurrentYear] && (WEEKNUM(NOW())-1)=[WeekOfYearNumber],1,0)

 

IsLast30Days

=if(AND([Date]>=[Today]-30,[Date]<=[Today] ),1,0)

 

YearWeekNum = Concatenate(Dates[Year],Dates[WeekOfYearNumber])

 

WTD = IF(CALCULATE(VALUES(Dates[YearWeekNum]),Dates[Date]=TODAY()-1,ALL(Dates))=Dates[YearWeekNum]

&& Dates[Date]<=TODAY()-1,"WTD",BLANK())

// shows if its in the current Week To Date - can use as a filter

 

RelativeDate = [Date]-Today()

//shows the difference in days between today and a date

// good for looking into the future or so many days back in the past.

 

EOM = EOMONTH(Dates[Date],0)
   //Add a column that returns true if the date on rows is the current date

 

IsLast7Days = if(AND([Date]>=[Today]-7,[Date]<=[Today]),1,0)

// 1 if is in the last 7 days

 

IsToday = Table.AddColumn(DayName, "IsToday", each Date.IsInCurrentDay([Date]))

//Column to see if it is the day today.

 

I use these extra columns all the time.

for your issue you can then just add filters on the page or the report for what you want.

 

Hopefully this will work.

Rgds

ED

View solution in original post

18 REPLIES 18

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.