Forum Discussion

nroeder07's avatar
nroeder07
Frequent Visitor
9 years ago
Solved

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.  ...
Need Help
  • nroeder07's avatar
    nroeder07
    9 years ago

    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. 

     

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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/

 

 

 

  • nroeder07's avatar
    nroeder07
    Frequent Visitor
    9 years ago

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

    • nroeder07's avatar
      nroeder07
      Frequent Visitor
      9 years ago

      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.