Power BI DAX工作日周列计算及月度周度目标分摊求助

Hey there, let's work through your DAX requirements for splitting monthly targets into weekly ones based on working days. I'll break down each of your three requests with clear, tested formulas that fit your existing column structure:

Use a simple IF statement to check your Working day column (1 = working, 0 = non-working) and return the date only for valid workdays:

Working Day Date = IF('YourTableName'[Working day] = 1, 'YourTableName'[Date], BLANK())

Note: Replace YourTableName with your actual table name.

To get a sequential number for each workday in the month (so you can use MAX() to fetch the total monthly workdays), use RANKX to rank workdays within the same year/month:

Working Day Number in Month = 
IF(
    'YourTableName'[Working day] = 1,
    RANKX(
        FILTER(
            'YourTableName',
            'YourTableName'[Year] = EARLIER('YourTableName'[Year]) &&
            'YourTableName'[Month] = EARLIER('YourTableName'[Month]) &&
            'YourTableName'[Working day] = 1
        ),
        'YourTableName'[Date],
        ,
        ASC,
        DENSE
    ),
    BLANK()
)

• EARLIER() ensures we only evaluate workdays in the same year/month as the current row.
• DENSE ranking avoids gaps from non-workdays (so the first workday = 1, second = 2, etc.).
• Use MAX('YourTableName'[Working Day Number in Month]) in a measure or column to get the total number of working days in the month.

Based on your target split logic, you need the count of working days within the current month's week (using your Weekinmonth column). Here's the DAX to add this as a column:

Weekly Working Days in Month = 
CALCULATE(
    COUNTROWS('YourTableName'),
    FILTER(
        'YourTableName',
        'YourTableName'[Year] = EARLIER('YourTableName'[Year]) &&
        'YourTableName'[Month] = EARLIER('YourTableName'[Month]) &&
        'YourTableName'[Weekinmonth] = EARLIER('YourTableName'[Weekinmonth]) &&
        'YourTableName'[Working day] = 1
    )
)

This counts all rows in the same year/month/week where Working day = 1—exactly the weekly workday count you need for target splitting.

Bonus: Full Target Allocation Columns #

Since your end goal is to split monthly targets into weekly ones, here are the extra columns you'll need (assuming your monthly target is stored in a column named Monthly Target and set on the 1st of each month):

Total Monthly Working Days Column #

Total Monthly Working Days = 
CALCULATE(
    COUNTROWS('YourTableName'),
    FILTER(
        'YourTableName',
        'YourTableName'[Year] = EARLIER('YourTableName'[Year]) &&
        'YourTableName'[Month] = EARLIER('YourTableName'[Month]) &&
        'YourTableName'[Working day] = 1
    )
)

Daily Target Column #

Daily Target = 
DIVIDE(
    LOOKUPVALUE(
        'YourTableName'[Monthly Target],
        'YourTableName'[Year], EARLIER('YourTableName'[Year]),
        'YourTableName'[Month], EARLIER('YourTableName'[Month]),
        'YourTableName'[Day in Month], 1
    ),
    'YourTableName'[Total Monthly Working Days],
    0 // Returns 0 if total workdays are 0 to avoid division by zero errors
)

Weekly Target Column #

Weekly Target = 'YourTableName'[Daily Target] * 'YourTableName'[Weekly Working Days in Month]

内容的提问来源于stack exchange，提问作者user12504122