How to Adjust "Date Preset" Logic to Match Business Week (Saturday–Friday) in Power BI?

[manoj_0911]

How to Adjust "Date Preset" Logic to Match Business Week (Saturday–Friday) in Power BI?

Hi Experts,

I'm working on a Power BI report for a utility client and received the following customer feedback regarding our date filtering presets:

---

Customer Feedback:

When the “DATE PRESET” option is selected, June 9–14 comes up.
Our business week begins on Saturday and ends on Friday.
Will the Power BI report be updated to reflect this?

---

Right now, our “This Week” logic defaults to a standard calendar week, which is returning incorrect ranges for their business use. For example, for the week of June 8–14 (Saturday to Friday), the preset is showing June 9–14 (Sunday to Friday) instead, which is causing confusion.

❓What I Need Help With:

• How to rewrite the logic to always reflect a Saturday–Friday business week.

• Any DAX best practices for implementing custom business weeks in a dynamic DatePeriods table.

Any guidance from the community would be very helpful — the customer is strict on this requirement and expecting this fix in the next release.

Thanks in advance! 🙏

Manoj Prabhakar

[v-sdhruv]

Hi @manoj_0911 ,

Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution  so other members can easily find it.
Thank You

[v-sdhruv]

Hi @manoj_0911  ,
Just wanted to check if you had the opportunity to review the suggestion provided?
Thank you @Nayan_Dholakia for your response to the query.
If the response has addressed your query, please accept it as a solution  so other members can easily find it.
Thank You

[Nayan_Dholakia]

DatePeriods =
UNION(

// Today
ADDCOLUMNS(
FILTER(CALENDAR(MIN('Date'[Date]), MAX('Date'[Date])), [Date] = TODAY()),
"Type", "Today",
"Sort", 1
),

// Yesterday
ADDCOLUMNS(
FILTER(CALENDAR(MIN('Date'[Date]), MAX('Date'[Date])), [Date] = TODAY() - 1),
"Type", "Yesterday",
"Sort", 2
),

// This Week (Saturday–Friday)
ADDCOLUMNS(
FILTER(
CALENDAR(MIN('Date'[Date]), MAX('Date'[Date])),
'Date'[Date] >= TODAY() - WEEKDAY(TODAY(), 16) &&
'Date'[Date] <= TODAY() - WEEKDAY(TODAY(), 16) + 6
),
"Type", "This Week",
"Sort", 3
),

// Last Week (Saturday–Friday)
ADDCOLUMNS(
FILTER(
CALENDAR(MIN('Date'[Date]), MAX('Date'[Date])),
'Date'[Date] >= TODAY() - WEEKDAY(TODAY(), 16) - 7 &&
'Date'[Date] <= TODAY() - WEEKDAY(TODAY(), 16) - 1
),
"Type", "Last Week",
"Sort", 4
),

// This Month
ADDCOLUMNS(
DATESBETWEEN(
'Date'[Date],
DATE(YEAR(TODAY()), MONTH(TODAY()), 1),
TODAY()
),
"Type", "This Month",
"Sort", 5
),

// Last Month
ADDCOLUMNS(
DATESBETWEEN(
'Date'[Date],
DATE(YEAR(EDATE(TODAY(), -1)), MONTH(EDATE(TODAY(), -1)), 1),
EOMONTH(TODAY(), -1)
),
"Type", "Last Month",
"Sort", 6
),

// This Quarter
ADDCOLUMNS(
DATESQTD('Date'[Date]),
"Type", "This Quarter",
"Sort", 7
),

// Last Quarter
ADDCOLUMNS(
PREVIOUSQUARTER(DATESQTD('Date'[Date])),
"Type", "Last Quarter",
"Sort", 8
),

// This Year
ADDCOLUMNS(
DATESYTD('Date'[Date]),
"Type", "This Year",
"Sort", 9
),

// Last Year
ADDCOLUMNS(
PREVIOUSYEAR(DATESYTD('Date'[Date])),
"Type", "Last Year",
"Sort", 10
),

// Custom
ADDCOLUMNS(
CALENDAR(MIN('Date'[Date]), MAX('Date'[Date])),
"Type", "Custom",
"Sort", 11
)
)

Add this calculated column in date table to group by business week start date:

BusinessWeekStart =
'Date'[Date] - WEEKDAY('Date'[Date], 16)

[v-sdhruv]

Hi @manoj_0911  ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution  so other members can easily find it.
Thank You

[Elena_Kalina · Accepted solution]

Hi @manoj_0911 

Try to replace your "This Week" and "Last Week" sections with this updated logic

// This Week (Saturday-Friday)
ADDCOLUMNS(
    FILTER(
        CALENDAR(MIN('Date'[Date]), MAX('Date'[Date])),
        [Date] >= DATEADD(TODAY(), -1 - WEEKDAY(TODAY(), 3), DAY) &&
        [Date] <= DATEADD(TODAY(), 5 - WEEKDAY(TODAY(), 3), DAY)
    ),
    "Type", "This Week",
    "Sort", 3
),

// Last Week (Saturday-Friday)
ADDCOLUMNS(
    FILTER(
        CALENDAR(MIN('Date'[Date]), MAX('Date'[Date])),
        [Date] >= DATEADD(TODAY(), -8 - WEEKDAY(TODAY(), 3), DAY) &&
        [Date] <= DATEADD(TODAY(), -2 - WEEKDAY(TODAY(), 3), DAY)
    ),
    "Type", "Last Week",
    "Sort", 4
),

Complete Updated DatePeriods Table

DatePeriods =
UNION(
    // Today (unchanged)
    ADDCOLUMNS(
        FILTER(CALENDAR(MIN('Date'[Date]), MAX('Date'[Date])), [Date] = TODAY()),
        "Type", "Today",
        "Sort", 1
    ),

    // Yesterday (unchanged)
    ADDCOLUMNS(
        FILTER(CALENDAR(MIN('Date'[Date]), MAX('Date'[Date])), [Date] = TODAY() - 1),
        "Type", "Yesterday",
        "Sort", 2
    ),

    // This Week (Saturday-Friday)
    ADDCOLUMNS(
        FILTER(
            CALENDAR(MIN('Date'[Date]), MAX('Date'[Date])),
            [Date] >= DATEADD(TODAY(), -1 - WEEKDAY(TODAY(), 3), DAY) &&
            [Date] <= DATEADD(TODAY(), 5 - WEEKDAY(TODAY(), 3), DAY)
        ),
        "Type", "This Week",
        "Sort", 3
    ),

    // Last Week (Saturday-Friday)
    ADDCOLUMNS(
        FILTER(
            CALENDAR(MIN('Date'[Date]), MAX('Date'[Date])),
            [Date] >= DATEADD(TODAY(), -8 - WEEKDAY(TODAY(), 3), DAY) &&
            [Date] <= DATEADD(TODAY(), -2 - WEEKDAY(TODAY(), 3), DAY)
        ),
        "Type", "Last Week",
        "Sort", 4
    ),

    // Rest of your periods (unchanged)
    // This Month, Last Month, etc...
)