Forum Discussion

rdini8's avatar
rdini8
Icon for Helper I rankHelper I
2 years ago

Week Number of a Particular Date Range

Hi everyone, I am currently working on a report which has a date range slicer. I want to be able to have another slicer/filter of week number based on the current date range. For example, if the date range is from 1st August to 2nd September, the first 7 days from 1st August would be week 1, then the next 7 days would be week 2 and so forth, till the end of the date range. 

Need Help

8 Replies

  • PijushRoy's avatar
    PijushRoy
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi rdini8 

    Please use below DAX

     

    WeekNumber = 
VAR StartDate = MIN('YourTable'[Date])
VAR CurrentDate = 'YourTable'[Date]
VAR WeekStartDate = STARTOFWEEK(StartDate, 1)
RETURN
    CEILING((CurrentDate - WeekStartDate) / 7, 1)

     


    If your requirement is solved, please make sure to MARK AS SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

    Thanks
    Pijush
    Linkedin

    • rdini8's avatar
      rdini8
      Icon for Helper I rankHelper I
      2 years ago

      The Dax just gives me a static number when used as a calculated column expression

      • Ahmedx's avatar
        Ahmedx
        Icon for Super User rankSuper User
        2 years ago

        you can't create a calculated column based on a slice, power bi doesn't support that

        I created a measure