Help DAX - conditional calculate

hi yoa380 ,

try like:

V_TOTAL QTY_NEW =

VAR selectedpicture = SELECTEDVALUE(SELECT_PIC[ShortDate])

RETURN

IF(

    YEAR(PROJECTION DATE) <= YEAR(TODAY()),

CALCULATE(

    SUMX(

       PROJECTION, 

       PROJECTION[TOTAL QTY]*PROJECTION[COST_WAC]

    ), 

    PROJECTION[PICTURE DATE]=selectedpicture

),

CALCULATE(

    SUMX(

       PROJECTION, 

       PROJECTION[TOTAL QTY]*RELATED(BUD[COST_BUD]

    ), 

    PROJECTION[PICTURE DATE]=selectedpicture

)

)

Hi yoa380 ,

Can you please use this and check does it works in the scenario you mentioned.

DAX:

V_TOTAL QTY_NEW =
VAR selectedpicture = SELECTEDVALUE(SELECT_PIC[ShortDate])
RETURN
    CALCULATE(
        SUMX(
            PROJECTION,
            PROJECTION[TOTAL QTY] *
            IF(
                YEAR(PROJECTION[PROJECTION DATE]) <= YEAR(TODAY()),
                RELATED(WAC[COST]),        -- Use WAC cost if projection date is in the current year or earlier
                RELATED(BUD[COST])         -- Use BUD cost if projection date is in the future
            )
        ),
        PROJECTION[PICTURE DATE] = selectedpicture
    )

This measure (V_TOTAL QTY_NEW) will dynamically adjust based on the year of PROJECTION DATE and multiply TOTAL QTY by either WAC or BUD cost as specified.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara.
Data Analyst | MSBI Developer | Power BI Consultant