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

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'

  • nroeder07's avatar
    nroeder07
    Frequent Visitor
    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

      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