topic Re: Create Previous Week Filter? in Desktop

Create Previous Week Filter?

cwayne758 — Wed, 04 Nov 2015 16:58:13 GMT

How would I go about creating a Previous Week filter in DAX?

Pretty straightforward, trying to capture a snapshot of business only in the previous week of that Year.

Below is the logic in my Access database

IIf(DatePart("ww",[SALES].[DATE_ENTERED])=DatePart("ww",Date())-1,"PREVIOUS",Null)

Re: Create Previous Week Filter?

PowerBIGuy — Wed, 04 Nov 2015 17:26:59 GMT

You could try the following solution seen here:

just change the filter to <7

http://community.powerbi.com/t5/Service/How-to-show-data-in-dashboard-for-current-month/m-p/9430

The following syntax worked for me: Date Filter = DATEDIFF(max('Date'[Date]), now(),DAY)

Re: Create Previous Week Filter?

cwayne758 — Wed, 04 Nov 2015 18:55:00 GMT

Thank you very much for your response PowerBIGuy. Unfortunately, I do not think this solution is working for my particular case (or I am mistakenly doing something)

Below is my usage of your suggestion.

I'm trying to create a column that lists each date as within the "Previous Week", if not, "Null"

Thank you again for your suggestions!

Re: Create Previous Week Filter?

PowerBIGuy — Wed, 04 Nov 2015 19:17:23 GMT

Maybe it's your data model. my example uses a date dim. Sorry it didn't work out.

Re: Create Previous Week Filter?

cwayne758 — Wed, 04 Nov 2015 19:28:38 GMT

I think it very well could be.

I retried, this time using my DateStream date table from Azure Marketplace

Error message: In DATEDIFF Function, Start Date cannot be greater than End Date.

When I switch my Start Date to reference dates in my FactTable, and End Date to reference dates in my DateStream table, my only result is 2.

Again, thank you for your replies!

Re: Create Previous Week Filter?

PowerBIGuy — Wed, 04 Nov 2015 19:30:50 GMT

No problem happy to help. I also got the same error. I Just filtered by date dim to exclude future dates to aviod the error.

Re: Create Previous Week Filter?

ALeef — Wed, 04 Nov 2015 19:30:59 GMT

cwayne758 : Are you needing to do this a lot? As in, do you need to be able to look at records that were entered only in the week previous to -right now-, or do you need to be able to look at a range of dates, and say "Well, the week of Jan 7,2007 had X Bookings, and the previous week had Y Bookings"?

If you only need to analyze the week leading up to right now, this point in time, just modify your query to only pull records with dates that are after NOW() -7.

If you need the latter, we need a bit more information about your data model. Then you are looking at creating measures based on what you need to see - like bookings in the previous week. etc...

Re: Create Previous Week Filter?

cwayne758 — Wed, 04 Nov 2015 19:44:52 GMT

ALeef I do this regularly, so it would definintely be the later.

Below is an image of my (amorphopus) data model. Im using two DimDate tables as they are connected to two distinct Date fields in my Fact Moves table and I need to be able to call on those date ranges at different points in my report.

Re: Create Previous Week Filter?

ALeef — Wed, 04 Nov 2015 19:55:49 GMT

What are you needing to show as far as previous week? Like I described earlier - "This is the week of Jan 7, 2007 and we had X Bookings, and the previous week we had Y Bookings"? That can be done with Measures using Datediff, as explained before.

If you need to track those over time, for instance : "This week we had 6 more bookings than last week, which had 3 less bookings than the week before, which had the same as the week before that" - you will need to make calculated columns. I would suggest making a column for "WeekEnding" in your DateDim table, and then using that in a filter to get WeekEnding - 7days, and filter on that.

But it all kind of depends on what you are needing metrics for. It looks like a pretty complicated data model.

Re: Create Previous Week Filter?

konstantinos — Wed, 04 Nov 2015 21:12:48 GMT

cwayne758 I am not sure what you truing to create but if you need a calculated column in Data table to use as a slicer you can try add a new calculated column

Previous week =
IF (
    AND (
        WEEKNUM ( 'Dates'[DateKey] )
            = WEEKNUM ( TODAY (); 2 ) - 1;
        'Dates'[YearKey] = YEAR ( TODAY () )
    );
    "Previous Week"
)

Re: Create Previous Week Filter?

cwayne758 — Wed, 04 Nov 2015 22:15:34 GMT

ALeef Sorry if I was/am being confusing. What I need to accomplish is a combination of the alternatives you suggested.

I have let's say, 5 years worth of Booking data. I would like to create a column/measure that allows me to use it as a Slicer with the option of "Previous Week" or "Null" in order to toggle between a view of the last 7 days and a view of the past 5 years.

The row in the "Previous Week" calculated column would say whether that corresponding Booking Entered Date was either in the "previous week" or "Null".

The issue with the Now()-7 is that it limits my entire query to just the previous 7 days, losing all the "Null" data that was entered earlier than the "previous week"

Re: Create Previous Week Filter?

cwayne758 — Wed, 04 Nov 2015 22:23:13 GMT

Thank you very much for your reply konstantinos. What i'm trying to create is a column that can be used as a slicer. I want the column to tell me whether the Date Entered for my sale was within the "previous 7 days" or "null".

The slicer will allow me to toggle between Historical-to-Date vies and Previous week views.

I tried using the code you provided but to no avail :( I changed the ' ; ' to ' , '

This is the error message I received. I get this message quite freqeuently. Could it be my data model is the issue?

Re: Create Previous Week Filter?

PowerBIGuy — Wed, 04 Nov 2015 23:13:01 GMT

Date Filter = IF(IF( MAX('Date'[Date]) <= NOW(),DATEDIFF(max('Date'[Date]), now(),DAY))<=7 && IF( MAX('Date'[Date]) <= NOW(),DATEDIFF(max('Date'[Date]), now(),DAY))>=1,"Previous 7 Days","")

Re: Create Previous Week Filter?

konstantinos — Thu, 05 Nov 2015 12:48:59 GMT

cwayne758This is not a model issue, usually this error is in measures or calculated columns happens when there is no row context since we are looking only for one date and they are more than one). Did you tried the calculated column in the Fact table? The formula needs to be in DIMDATE. Else you can try wrap the formula with MAX/MIN or use PowerBIGuy formula

Re: Create Previous Week Filter?

cwayne758 — Thu, 05 Nov 2015 13:00:10 GMT

konstantinos Haha thank you so very much!! It works beautifully. I was doing it in the Fact table and not the DIMDATE.

PowerBIGuy ALeef Thank you also very much. Great community.

Re: Create Previous Week Filter?

ALeef — Thu, 05 Nov 2015 13:00:29 GMT

Looks like PowerBIGuy has it right. I was misunderstanding the problem. Your explanation helped, but I didn't see it until this morning!

Hope that works for ya!

Re: Create Previous Week Filter?

cwayne758 — Fri, 06 Nov 2015 20:01:14 GMT

How would you go about creating a similar measure for "Previous 8 Weeks"?

I am not sure why PowerBIGuy's formula is not working for me.

Thank in advance! :)

Re: Create Previous Week Filter?

konstantinos — Fri, 13 Nov 2015 10:04:03 GMT

If you need to have as a slicer different custom time periods you need to use "disconnected slicers" and measures for all time periods. Search in powerpivotpro.com as there are many articles about it like:

http://www.powerpivotpro.com/2013/06/simplifying-time-calculations-and-the-user-experience-using-disconnected-slicers/

Re: Create Previous Week Filter?

cwayne758 — Fri, 13 Nov 2015 14:02:21 GMT

Thanks so much... exactly what I am looking to do

Re: Create Previous Week Filter?

WorkHard — Fri, 24 Jan 2020 16:55:50 GMT

For the future reader, the date filter now has a "calendar week" option so it looks like this has been addressed.