如何用DAX编写逻辑？求生成按projectid分组的[lastmonth]列DAX查询

Got it, let's break down how to create this lastmonth column exactly as you need it. The core idea is to group by ProjectID, collect all fiscal months where TPH has a value, then find the immediate preceding month relative to the current row's fiscal month.

Quick Assumptions #

Let's assume your table is named ProjectData, and it includes:

• ProjectID: The grouping key for your projects
• TPH: The column we need to check for non-blank values
• Date: A date column we'll use to calculate the fiscal month (skip this part if you already have a dedicated FiscalMonth column in your table)

DAX Calculated Column Code #

Here's the full DAX expression to create your lastmonth column:

lastmonth = 
// Capture the project ID from the current row
VAR CurrentProject = [ProjectID]
// Calculate the fiscal month for the current row (adjust this if your fiscal calendar is non-standard)
VAR CurrentFiscalMonth = 
    FORMAT([Date], "YYYY-MM") // Uses natural calendar months; tweak if your fiscal year starts elsewhere
// Get all unique fiscal months for this project where TPH is not blank
VAR ValidFiscalMonths = 
    CALCULATETABLE(
        VALUES(FORMAT(ProjectData[Date], "YYYY-MM")),
        ProjectData[ProjectID] = CurrentProject,
        NOT(ISBLANK(ProjectData[TPH])),
        ALL(ProjectData) // Ensure we look across all rows for the project, regardless of filters
    )
// Sort valid months in descending order to easily find the previous one
VAR SortedMonths = SORT(ValidFiscalMonths, [Value], DESC)
// Find the position of the current fiscal month in the sorted list
VAR CurrentMonthPosition = XMATCH(CurrentFiscalMonth, SortedMonths)
// Return the previous month if it exists; otherwise return blank
RETURN
    IF(CurrentMonthPosition > 1, INDEX(CurrentMonthPosition - 1, SortedMonths), BLANK())

Customizing for Non-Standard Fiscal Calendars #

If your fiscal year doesn't align with the natural calendar (e.g., starts in April), update the CurrentFiscalMonth calculation to match your organization's fiscal rules. For example:

CurrentFiscalMonth = 
VAR FiscalYear = IF(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date]) - 1)
VAR FiscalMonthNumber = IF(MONTH([Date]) >= 4, MONTH([Date]) - 3, MONTH([Date]) + 9)
RETURN FORMAT(FiscalYear, "0000") & "-" & FORMAT(FiscalMonthNumber, "00")

How This Works #

1. CurrentProject: Grabs the project ID from the row we're calculating to scope our logic to that specific project.
2. CurrentFiscalMonth: Converts the row's date into a standardized fiscal month string (adjust this formula to match your fiscal calendar setup).
3. ValidFiscalMonths: Uses CALCULATETABLE to fetch all unique fiscal months for the current project where TPH isn't blank.
4. SortedMonths: Sorts these valid months in descending order so we can quickly locate the immediate preceding month.
5. CurrentMonthPosition: Finds where the current fiscal month sits in the sorted list of valid months.
6. Final Return: If there's a preceding month (position > 1), we return it; otherwise, we return blank (since there's no prior valid month for that project).

内容的提问来源于stack exchange，提问作者p.ganesh kumar