cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nroeder07
Frequent Visitor

Custom Week filter

Sorry if this is a simple question however, I am new to Power BI, Power Query, DAX. I do however have expereince with sql, php, excel, etc.

 

My company runs a custom sales week Tuesday - Monday. 

 

I need to be able to filter my reports on Current Week and Previous Week. 

 

Example Today is Tuesday 10/18. So my current sales week would be 10/18-10/24

My previous sales week would be 10/11-10/17. 

 

I'm assuming that it will invovle in either (A) creating a custom column in my date table and/or (B) creating a custom date range table.

 

Thanks for the help. Please give detailed instructions and pretend like you're talking to an English major. This is all new to me. 

1 ACCEPTED SOLUTION

This works

 

EstimateWeek =[Estimate Date Year Number]*100+Date.WeekOfYear([Estimate Date],2)

TodayWeek=Date.Year(DateTime.LocalNow())*100+Date.WeekOfYear(DateTime.LocalNow(),2)

PreviousSalesWeek=if [EstimateWeek]-[TodayWeek]=-1 then 0 else 1

 

However I'm going to have an issue on the year transition. 

 

 

View solution in original post

7 REPLIES 7
GilbertQ
Super User II
Super User II

Hi there,

 

You could look at my data story and the associated blog post, which would enable you to create any periods that you want. Which you could then use to easily filter, as well as create measures.

 

https://community.powerbi.com/t5/Data-Stories-Gallery/Create-Dynamic-Periods-for-Fiscal-or-Calendar-...





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

If we go by option (A)

 

In your dateTable (or wherever your dates are) enter the following as a calculated column formula:

 

Custom Week that starts on Tuesdays =
WEEKNUM ( DimDate[Date] - 1; 2 )

Now all weeks start on Tuesdays and end on Mondays

 

Edit:

Dimdate being 'YourTable" and [Date] being 'YourDateColumn'

This works

 

EstimateWeek =[Estimate Date Year Number]*100+Date.WeekOfYear([Estimate Date],2)

TodayWeek=Date.Year(DateTime.LocalNow())*100+Date.WeekOfYear(DateTime.LocalNow(),2)

PreviousSalesWeek=if [EstimateWeek]-[TodayWeek]=-1 then 0 else 1

 

However I'm going to have an issue on the year transition. 

 

 

View solution in original post

Hi @nroeder07,

 

>>However I'm going to have an issue on the year transition. 

You can add a condition to check the week number. If week number =1, you can use the date range of previous year 12.31 's week to filter.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
scottsen
Memorable Member
Memorable Member

Yes, you will certainly want to have a custom column in your calendar table... probably (at least) 2.  One that is "Week Number In Year" and one that is "Week Id"... where the former is 1-52 (ish), and the latter also increments across years (so 1-157'ish across 3 years of calendar).   I can image other useful columns like "Week Ending Date" or some sort of date rangy column "10/18 - 10-24" for nice placement on rows.

 

If your date table is already coming from sql and you are comfortable there... I would just add the columns there.  You can also write calc columns -- think my article on p3's site has something similiar::  http://www.powerpivotpro.com/2014/04/week-ending-date-calculation/

 

 

 

Ok I think i've made progress and in the process become more lost. 

 

So i've added 4 columns

 

Date.StartOfWeek([EstimateDate],2)

Date.EndOfWeek([EstimateDate],2)

Date.DayOfWeek([EstimateDate],2)

Date.WeekOfYear([EstimateDate],2)

 

Which gives me the correct start date of each week, correct end day of each week, and the proper week count.

 

Now I have zero clue how to use this in my filter on my report... 

Figured it out I think 

 

EstimateWeek =[Estimate Date Year Number]*100+Date.WeekOfYear([Estimate Date])

TodayWeek=Date.Year(DateTime.LocalNow())*100+Date.WeekOfYear(DateTime.LocalNow())

PreviousSalesWeek=if [EstimateWeek]-[TodayWeek]=-1 then 0 else 1

 

Set filter on report to PreviousSalesWeek = 0.

 

Let me know if i've done anything wrong. 

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.