Forum Discussion
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.
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.
7 Replies
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.
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.
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
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.
