Forum Discussion

amirghaderi's avatar
amirghaderi
Icon for Helper IV rankHelper IV
4 years ago
Solved

Rolling Date (full week) Filter

I have chart which filter the scale from 30 days ago to 150 days in future. There is a column which define this scale and has been assigned as the filter to the chart. I want to have this filter to be from 4 weeks ago to 20 weeks in future. as you see in the below screenshot, since today is the middle of the week, the value for 4 weeks ago, is only half of the week value. Basically, the week to be calculated from start of the week and not today in the filter column formula.

 

 

Rolling Month = If(and(DATEDIFF(today(),'Date'[Date],DAY)>-30,DATEDIFF(today(),'Date'[Date],DAY)<150), "Include","Exclude")"
 

 

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi amirghaderi 

     

    Assume your week starts on Monday and ends on Sunday, you can use the following column formula.  

    Rolling Month =
IF (
    AND (
        'Date'[Date] > TODAY () - WEEKDAY ( TODAY (), 2 ) - 28,
        'Date'[Date] < TODAY () - WEEKDAY ( TODAY (), 2 ) + 140 + 1
    ),
    "Include",
    "Exclude"
)

     

    TODAY () - WEEKDAY ( TODAY (), 2 ) will return the date of last week's Sunday. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

     

     

2 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi amirghaderi 

     

    Assume your week starts on Monday and ends on Sunday, you can use the following column formula.  

    Rolling Month =
IF (
    AND (
        'Date'[Date] > TODAY () - WEEKDAY ( TODAY (), 2 ) - 28,
        'Date'[Date] < TODAY () - WEEKDAY ( TODAY (), 2 ) + 140 + 1
    ),
    "Include",
    "Exclude"
)

     

    TODAY () - WEEKDAY ( TODAY (), 2 ) will return the date of last week's Sunday. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

     

     

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    amirghaderi , you can work using week rank

     

    new columns
    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
    Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
    Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

     

    measures
    This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
    Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
    Last 8 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

     


    Last 4 weeks = CALCULATE(Averagex(Values('Date'[Week Rank]), [Net Change in Price and Cost]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

     

    Power BI — Week on Week and WTD
    https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
    https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
    https://www.youtube.com/watch?v=pnAesWxYgJ8