Forum Discussion

shamwoo's avatar
shamwoo
Frequent Visitor
7 years ago

Creating 7-Day Period Range for Reporting Based on Date

Hello All,

 

I'm not sure if creating a calendar is needed for this but I'm trying find a way to convert a date to a weekly range for use in reporting. For example, when the date falls within a respective SundayDateYear - SaturdayDateYear range, then the weekly range would be that SundayDateYear - SaturdayDateYear. Then I would use that SundayDateYear - SaturdayDateYear range as a filter mechanism on my desktop visual. 

 

The below formula works in Excel but it is not working the same in Power BI.

 

TEXT('xyz'-COLUMN+7-MOD('xyz'-1,7)+(COLUMNS('xyz')-1)*7, "mm/dd/yy")&" - "&TEXT('xyz'+7-MOD('xyz'-1,7)+(COLUMNS('xyz')-1)*7+6, "mm/dd/yy")

 

I am thankful for any recommendations and tips.

 

 

2 Replies

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

    Hi shamwoo

    Could you show me which columns in excel you used in this formula and what the result of this formula so that it is clear for me to analyze?

     

    Best Regards

    Maggie

    • shamwoo's avatar
      shamwoo
      Frequent Visitor
      7 years ago

      Hi Maggie, 

       

      I sincerely appreciate you for reaching out! Please find an example of what I am looking at. 

       

      Column A - Column E contains dates on when the row item was handled. Column F contains the date when the row item was last handled. I am looking for a DAX that creates a period range (Saturday to Sunday) using Column F as a comparison column, but bases whichever date is closest in Column A - Column E as the beginning of the period range.

       

      For example, on the second to last row the Last Modified Date was 08/20/18 and searching through Column A - Column E, Column B has the nearest date. So a Period Range of 08/16/18 - 08/22/18 will be created.