Create Previous Week Filter?

[cwayne758]

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)

[WorkHard]

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

[PowerBIGuy]

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)

[cwayne758]

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!

[ALeef]

@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...

[cwayne758]

@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. 

[ALeef]

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.

[cwayne758]

@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"

[konstantinos]

@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"
)

[cwayne758]

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? 

[konstantinos]

@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

[cwayne758]

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! 🙂 

[konstantinos · Accepted solution]

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-dis...

[cwayne758]

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

[ALeef]

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!

[cwayne758]

@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. 

[PowerBIGuy]

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","")

[PowerBIGuy]

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

[cwayne758]

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! 

[PowerBIGuy]

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.