cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cwayne758 Helper IV
Helper IV

Create Previous Week Filter?

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)

1 ACCEPTED SOLUTION

Accepted Solutions
konstantinos Memorable Member
Memorable Member

Re: Create Previous Week Filter?

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

Konstantinos Ioannou

View solution in original post

19 REPLIES 19
PowerBIGuy Responsive Resident
Responsive Resident

Re: Create Previous Week Filter?

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)

Business Intelligence Architect / Consultant
cwayne758 Helper IV
Helper IV

Re: Create Previous Week Filter?

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"

 

Date Filter.PNG

Thank you again for your suggestions!

PowerBIGuy Responsive Resident
Responsive Resident

Re: Create Previous Week Filter?

 

 

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

 

 

Capture.JPG

 

 

Business Intelligence Architect / Consultant
cwayne758 Helper IV
Helper IV

Re: Create Previous Week Filter?

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 Responsive Resident
Responsive Resident

Re: Create Previous Week Filter?

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. 

Business Intelligence Architect / Consultant
ALeef Advocate III
Advocate III

Re: Create Previous Week Filter?

@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 Helper IV
Helper IV

Re: Create Previous Week Filter?

@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. Data Model.PNG

ALeef Advocate III
Advocate III

Re: Create Previous Week Filter?

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.

konstantinos Memorable Member
Memorable Member

Re: Create Previous Week Filter?

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

 

Konstantinos Ioannou

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors