Forum Discussion
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.
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 yearIsInLastWeek
=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 dateIsLast7Days = 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
18 Replies
These questions are impossible to answer without some concept of your data. For example, if you have a date field, then you could use WEEKNUM function to get the current week for that date. You could have another column that used WEEKNUM(TODAY()) to get the current week number and a third column that returns 1 if they are equal. Then you just have to set your filter to only display rows with a 1 in that third column. Similar technique could be used for month to date.
Or, you might be able to use some of the Time Intelligence functions to get you there:
Impossible to say without some of your actual data.
Thank you for the tip. I was referring to one of the Salesforce object tables called "Opportunity", where I wanted to specify different time periods on the CloseDate column for different graphs.
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 yearIsInLastWeek
=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 dateIsLast7Days = 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
I am new in using Power BI, but for me it is a little bit strange that it is so "complicated" to display values for the current month. Call me stupid, but in my opinion this should be possible just with one click in the chart properties or somehting like that.
For me the solution with the extra column and 0 or 1 values fitted the best, but I have an additional question which refers to the problem above. Is it possible to display a Text with the current month in the Dashboard like a headline?
Hi Christian I have the same issue.
To get around this I created a column called Month-Year and then put it on the report as a Multi-Row-Card visual.
When the filters applied to the page change the value in that column will change. The top Jan-2016 is driven by what is selected in the filters. e.g.This works as filters are applied.
Other option could be if you applied the isinlastmonth = 1 as a filter on the entire page. This will remove the need for those two manual filters to constantly be changed and should just always show the previous month.
Rgds, ED
elliotdixonCould you help out with few calculated columns for the datedimm table, please.
I would need some helpo with following dimensions:
Last Week to Date (i.e. is date between week 1 and last week)
Last Week previous year to date
Current Quarter (I have addeda new column QtrofYear dispalying quarter number.)
Current quarter previous year
Quarter X
Quarter X previous year
Thank you.
