How to Adjust "Date Preset" Logic to Match Busines...

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.

✅ Current Setup:

Date = 
CALENDAR(
    MIN('DATE_TIME_V'[TXN_DATE]),
    MAX('DATE_TIME_V'[TXN_DATE])
)

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
ADDCOLUMNS(
    FILTER(
        CALENDAR(MIN('Date'[Date]), MAX('Date'[Date])),
        WEEKNUM([Date], 1) = WEEKNUM(TODAY(), 1) && YEAR([Date]) = YEAR(TODAY())
    ),
    "Type", "This Week",
    "Sort", 3
),

// Last Week
ADDCOLUMNS(
    FILTER(
        CALENDAR(MIN('Date'[Date]), MAX('Date'[Date])),
        WEEKNUM([Date], 1) = WEEKNUM(TODAY(), 1) - 1 && YEAR([Date]) = YEAR(TODAY())
    ),
    "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
)
)

❓ 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

Elena_Kalina

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...
)