Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Week Number or Week Ending on a Wednesday

I am doing a report looking at data over the past 4 weeks.  To do this, I simply created a column called Current Week Number and a column called Week Relative to Today.  My report would include data with a Week Relative to Today of 1-4.  Now, we have decided that we would like to run this report with a week ending day of Wednesday.  I have a date table and I used this formula to have the week end day displayed as Wednesday.

 

Week End Date = CALCULATE(Max('Calendar'[Date])-3, FILTER('Calendar','Calendar'[WeekNum] = EARLIER( 'Calendar'[WeekNum])))

 

This formula works well as every week now shows a week end day of Wednesday.  However, I now need my Week Numbers to align with the week ending day of Wednesday.  I would then need my Current Week Number formula to consider the week ending day of Wednesday.  

 

Any help on this would be appreciated.

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous

    Modifcation to my reply

    weeknum-based on end of wednesday = IF([weekday]<=4,[weeknum]-1,[weeknum])

week end date =
    IF (
        [week-day] = "day3",
        [date],
        CALCULATE (
            MIN ( [date] ) + 6,
            FILTER (
                Sheet2,
                Sheet2[weeknum-based on end of wednesday]
                    = EARLIER ( Sheet2[weeknum-based on end of wednesday] )
            )
        )
    )

     

    Best Regards

    Maggie

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous

    Assume the original weeknum starting from Sunday, Weekday is "Sunday=1 through Saturday=7", thus Wednesday shows 4 for Weekday.

    weeknum = WEEKNUM([date],1)

weekday = WEEKDAY([date],1)

    Then create calculated columns

    week end date = CALCULATE(MAX([date])-3,FILTER(Sheet2,Sheet2[weeknum]=EARLIER(Sheet2[weeknum])))

starting from wednesday = IF([weekday]<=4,[weeknum]-1,[weeknum])

     

    Best Regards

    Maggie

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you, v-juanli-msft. My only remaining concern is that some of the dates are now greater than the week end date.  For example, on your chart, 8/24/18 has a week end date of 8/22/18.  

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        7 years ago

        Hi Anonymous

        Modifcation to my reply

        weeknum-based on end of wednesday = IF([weekday]<=4,[weeknum]-1,[weeknum])

week end date =
        IF (
            [week-day] = "day3",
            [date],
            CALCULATE (
                MIN ( [date] ) + 6,
                FILTER (
                    Sheet2,
                    Sheet2[weeknum-based on end of wednesday]
                        = EARLIER ( Sheet2[weeknum-based on end of wednesday] )
                )
            )
        )

         

        Best Regards

        Maggie