如何在数据模型中用公式实现季度费用的条件赋值？

Got it, let's solve this quarterly accrual requirement you have. Below are simple, actionable formulas for the most common tools used in building data models:

Excel & Google Sheets #

If your "Month" column contains numeric values (1, 2, 3...12) or full dates, use one of these approaches:

Option 1: Use MOD Function (Clean for Numeric Months) #

This checks if the month number is divisible by 3 (since quarter-end months are multiples of 3):

=IF(MOD(A2, 3) = 0, $B$1, 0)

• A2: The cell containing your month number/date
• $B$1: The cell with your specified quarterly expense amount (use absolute reference so it doesn't shift when you drag the formula down)
• How it works: MOD(A2,3) returns 0 only when the month is 3,6,9,12—triggering the expense amount; otherwise returns 0.

Option 2: Use OR Function (More Intuitive for Beginners) #

Explicitly check if the month is a quarter-end value:

=IF(OR(A2=3, A2=6, A2=9, A2=12), $B$1, 0)

This is great if you want to avoid math operations and keep the logic easy to read at a glance.

For Date-Formatted Months #

If your "Month" column has full dates (e.g., 1/15/2024), wrap the cell reference in MONTH() to extract the numeric month:

=IF(MOD(MONTH(A2), 3) = 0, $B$1, 0)

Power BI (DAX Formula) #

If you're building this in a Power BI data model, use this DAX measure or calculated column:

Quarterly Accrual = 
IF(
    MONTH('YourTable'[Month]) IN {3, 6, 9, 12},
    'YourTable'[Specified Expense Amount],
    0
)

• Replace 'YourTable' with your actual table name
• IN {3,6,9,12} checks if the extracted month falls into the quarter-end list

Example Output #

Applying any of these formulas will give you the exact result you need:

Pro tip: If you need to handle fiscal quarters that don't align with calendar quarters (e.g., fiscal year starts in July), just adjust the month values in the formula to match your fiscal quarter-end months (e.g., 9,12,3,6).

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