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

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.

MonthSummary.png

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.

Greg_Deckler
Super User
Super User

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:

https://support.office.com/en-US/article/Time-Intelligence-Functions-DAX-0571dcda-e4e8-42a8-b205-e1f...

 

Impossible to say without some of your actual data.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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. 

 

opportunity object.png

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

Anonymous
Not applicable

Thank you so much!

IsLast7Days = if(AND([Date]>=[Today]-7,[Date]<=[Today]),1,0) // 1 if is in the last 7 days

 

 

Can we make the above formula to show a cumulative total for the last 7 days....

Great post by @elliotdixon! So you've got a bunch of filters to determine whether something falls within last 7 days, or last 30 days, or this year. This is fine if we want to apply just one of the filters to the dashboard. But what if we want the user to select which filter to show? For example, say we have these three filters:

 

IsInCurrentWeek

IsInLastWeek

IsLast30Days

 

Can we show a single filter in the dashboard so that the user can select which one of those three filters (current week, last week, or last 30 days)?

 

In Tableau, these 'previous week', 'previous month', 'previous 6 month' (etc.) type selectors are already built in.

 

Thank you so much @elliotdixon !

 

These have been invaluable in designing my first forays into Power BI and helping me get my head around DAX for the first time.

 

In case other folks are looking here for an 'IsInCurrentFiscalYear' calculated column that returns 1 or 0, here's one that's working for me:

 

IsInCurrentFY = CALCULATE(sumx(dates,if(
		dates[year] = year(today())-1
		&& dates[month] >= 7, 1,
			if(dates[Year]=YEAR(TODAY())
			&& month(today()) < 7
			&& month(Dates[Date]) < 7, 1,
				if(dates[year]=year(today()) 
				&& month(today()) > 6
				&& Dates[Month] > 6, 1,
	0)))))

 

Hope this checks out...

 

Thanks again,

Adrian

@AdrianThread, assuming you have a FiscalYear field in your date dimension:

 

// Boolean flag, true in current fiscal year
CurrentFY =
VAR
    CFY =
        LOOKUPVALUE(
            DimDate[FiscalYear]
            ,DimDate[Date]
            ,TODAY()
        )
RETURN
DimDate[FiscalYear] = CFY



// Integer flag, 1 in current fiscal year
CurrentFY =
VAR
    CFY =
        LOOKUPVALUE(
            DimDate[FiscalYear]
            ,DimDate[Date]
            ,TODAY()
        )
RETURN
1 * (DimDate[FiscalYear] = CFY)

It's best to avoid duplicating the same logic in multiple fields. Since you likely have the same logic in a [FiscalYear] field as in your definition of [IsInCurrentFiscalYear], you'd have to worry about keeping them in sync if you find an error. With the constructions above, you only define the fiscal year logic in one place and updates to it are automatically propagated.

Thanks @greggyb, I don't have a fiscal year field in my raw data so I needed to make a measure. I certainly agree with not duplicating logic over multiple fields and measures. Thanks again.

Hi @AdrianThread - great to hear the date codes are working for you.

I tried your code for Fiscal Year however got an error.

 

DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

 

I think it has to do with the Month - what is the format of your month column?
I have MonthNumberOfYear and its just a whole number 1 - 12.

 

Cheers

 

@elliotdixon Hello!

 

Thank you for your great suggestions. I've also found value in them!

 

Is there any possibility to create a measure which displays sum of sales for current week and month?

I'm trying to do this because in the same screen i need to display both values in the same screen and using a slicer does not work for me because it will filter all my data.

 

I've been searching the web for the past days to come up with a formula but no succes.

It would be of great help to me if i could find a solution to this.

 

BR,

Andrei

 

 

Both year and month in your date dimension must be integers with the whole number data taype in @AdrianThread's calculated column.

I find it easier to use:

 

=Date.IsInCurrentYear 

=Date.IsInCurrentWeek

=Date.IsInCurrentMonth

 

all of these functions can be used in the Query Editor.

 

Just another method of creating the same output.

@cwayne758 Hi can you show me a screen grab of where you put this along with an example use of the funciton? Thanks in advance!

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.