DAX Measure with multiple conditions

[Roland74]

Hi,

I'm struggling to create a measure with multiple conditions. Hope you can help!

I'm on direct query, can't easily change the model. I've created a sample, and the model looks like:

The objective is to create a table, and when the user selects a month, the correct value shows. But not for every month i have a value in the fact-table. The conditions the measure has to have:
(1) In the DIM-ProjectPreclosure the field 'Posted' must be TRUE

(2) If a date has 2 or more rows in the fact-table, the one with the highest Line-number in de DIM-ProjectPreclosure is right.

     In the example, Prj. A has two values om 30/06/2024, the one with linenumber 70000 is the right one

(3) Months before the first postingdate are 'empty'. In the previous example, the first postingdate is february, so january is empty.

(4) Missing months get the value of the latest month. Example:

    Project B has a value for february, but the next postingdate with TRUE is in July. So march, april, may and june get the value of         february

(5) In the example of project A, in June a value was posted which is not yet corrected (correctionposted = False). In the months after june until now the june-number has to be reported

(6) If the correction is posted (correctionPosted = TRUE) AND de CorrectionDate is N/A, the value has to be reported until the latest month. Example:

Project C has Linenumber 30000 posted in March. This line is corrected in July (DateModified = 26/07/2024).

The value of Line 30000 has to be reported in the months March, April, May and June. In July it's empty, because it's corrected.

Hope someone can point me in the right directions. I've got all the single values, but struggle to get it into one measure.

[Roland74]

Can't figure it out 🙄, solution of DataNinja777 doesn't work, because i can't share my model and that solution refers to DIMProjectPreclosure[Date], a field i don't have.

I will keep on struggling.... Eventualy i will find a solution

[v-jialongy-msft]

Hi @Roland74 

Has your problem been resolved? If so, could you mark the corresponding reply as the solution so that others with similar issues can benefit from it?

Best Regards,

Jayleny

[Roland74]

I finaly got the measure right for the row-level, however the totals are way off. Any suggestions.

Measure with the right row-values for the projects:

ProvisionLoss(Def) =
------------------- Last LineNumber in Dim-table ---------------------
------------------- (Posted = TRUE && PostedMonth <= SelectedMonth ) ------------------------------
VAR MaxLineNumberSelected = 
            CALCULATE(
                MAX(ProjectPreclosureResult[LineNumber]),
                PostingDate[CYearMonthNo] <= SELECTEDVALUE('Date'[CYearMonthNo]),
                ProjectPreclosureResult[Posted] = "TRUE",
                CROSSFILTER(factProjectPreclosureResult[ProjectPreclosureResultID], ProjectPreclosureResult[ProjectPreclosureResultID], Both)
                )

------------------------- Closed Project have no valua after the last ModefiedDate ------------------------------
------------------------- CorrectionPosted = TRUE && CorrectionPostedBy = N/A -----------------------------------
VAR LastDateModified =
            CALCULATE(
                MAX(DateModified[CYearMonthNo]),
                ProjectPreclosureResult[CorrectionPosted] = "TRUE" && ProjectPreclosureResult[CorrectionPostedBy] = "N/A",
                CROSSFILTER(factProjectPreclosureResult[ModifiedDateID], DateModified[DateID], Both)
                )

-------- Alternative, if LastDateModified = Blank --------
VAR IfLastDateModifiedIsBlank = SELECTEDVALUE('Date'[CYearMonthNo]) +1

VAR MaxDateCorrection = IF (ISBLANK(LastDateModified), IfLastDateModifiedIsBlank, LastDateModified)

--------- Final Measure-----------------------------------------------------------------------------------------------------

VAR ProvisionLossSelected =
            ROUND(
            CALCULATE(
                SUMX(factProjectPreclosureResult, factProjectPreclosureResult[CorrectedProvisionLoss_PPR]),
                ProjectPreclosureResult[LineNumber] = MaxLineNumberSelected &&
                SELECTEDVALUE('Date'[CYearMonthNo]) < MaxDateCorrection
                )
            ,2)

RETURN
        IF(ProvisionLossSelected = 0, BLANK(), ProvisionLossSelected)

[DataNinja777]

Hi @Roland74 ,

To create a DAX measure that satisfies the specified conditions, we first filter the data to include only rows where Posted = TRUE. For dates with multiple rows, we identify the row with the highest LineNumber using SUMMARIZE. To exclude months before the first posting date, we calculate the minimum posting date per project using CALCULATE with a MIN filter. Missing months are handled by propagating the latest valid value using a filter on dates up to the current month. For corrections, if CorrectionPosted = FALSE, the last valid value is propagated; if CorrectionPosted = TRUE and CorrectionDate is N/A, the value is propagated; otherwise, it stops after the correction date. Below is the combined DAX formula:

FinalMeasure =
VAR PostedData =
    FILTER(
        'DIM_ProjectPreclosure',
        'DIM_ProjectPreclosure'[Posted] = TRUE
    )

VAR MaxLinePerDate =
    SUMMARIZE(
        PostedData,
        'DIM_ProjectPreclosure'[Date],
        "MaxLineNumber", MAX('DIM_ProjectPreclosure'[LineNumber])
    )

VAR FirstPostingDate =
    CALCULATE(
        MIN('DIM_ProjectPreclosure'[Date]),
        FILTER(PostedData, 'DIM_ProjectPreclosure'[Posted] = TRUE)
    )

VAR LastValue =
    MAXX(
        FILTER(
            PostedData,
            'DIM_ProjectPreclosure'[Date] <= MAX('Calendar'[Date])
        ),
        'DIM_ProjectPreclosure'[Value]
    )

VAR CorrectionLogic =
    IF(
        'DIM_ProjectPreclosure'[CorrectionPosted] = TRUE,
        IF(
            ISBLANK('DIM_ProjectPreclosure'[CorrectionDate]),
            LastValue,
            BLANK()
        ),
        LastValue
    )

RETURN
IF(
    MAX('Calendar'[Date]) < FirstPostingDate,
    BLANK(),
    CorrectionLogic
)

This measure combines all conditions and ensures that the results dynamically adjust based on the selected month, following the rules for posting, corrections, and missing months.

Best regards,

[Roland74]

Hi DataNinja777,

Thanx for your solution. I didn't have time till now to check....

I don't have a date in the DIM_ProjectPreclosure, so it doesn't work.

I'll try to figure it out, you've put me in the right direction (i hope)