Creating 7-Day Period Range for Reporting Based on Date

[shamwoo]

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.

[v-juanli-msft]

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]

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.