Help DAX - conditional calculate

[yoa380]

Dear All,

I need your help for my below need. Here the explanations :

To make it simpler, let's say I have 1 fact table called "PROJECTION" in which I project the stock of my components. In the attached picture I only have 2 components A & B.

Every month I take "a picture" of this data in my ERP and thanks to Query I combine all pictures in the PROJECTION table.

The column "PICTURE DATE" is the month I have extracted the data (here only OCT24 & NOV24). In each picture, we project for each item a quantity available and a quantity blocked AND for each end of coming months (that are in column "PROJECTION DATE").

I already created a DAX measure called "TOTAL QTY" as per : 

TOTAL QTY =

CALCULATE(SUMX(PROJECTION, PROJECTION[QTY ava]+PROJECTION[QTY blk]))

Then I have 2 dimension tables containing items costs. One table "WAC" contains for each item their weighted average cost and The other "BUD" contains for each item their budget cost.

In my final report, I let the user select the PROJECTION picture he wants through a segment call "SELECT_PIC". And in a matrix I put items in lines and Year/Month of PROJECTION DATE in columns.

The value in the matrix is the valuation of items stock. Until now I was valuating my TOTAL QTY with only the cost of WAC table.

Cost that I also integrated in my fact table as "COST_WAC" through a new column (possible thanks to a relationship between tables)

COST_WAC = RELATED(WAC[COST])

My measure was : 

V_TOTAL QTY =

VAR selectedpicture = SELECTEDVALUE(SELECT_PIC[ShortDate])

RETURN

CALCULATE(SUMX(PROJECTION, PROJECTION[TOTAL QTY]*PROJECTION[COST_WAC]), PROJECTION[PICTURE DATE]=selectedpicture)

My new need for which I need your help is that I want to introduce a IF condition kind of logic somewhere.

We will still select a PICTURE DATE on the report but the valuation of stock should now be dependent of "PROJECTION DATE".

If I select PICTURE DATE = 01/11/2024 for my attached example it's the blue lines that will be taken. My matrix will display 3 columns PROJECTION DATE = 11/2024, 12/2024 and 01/2025.

My new valuation (let's call V_TOTAL QTY_NEW) should multiply the TOTAL QTY by the cost of the item from the WAC cost table if YEAR(PROJECTION DATE) <= YEAR(NOW)

&

should mutiply TOTAL QTY by the cost of the item from the BUD cost table if YEAR(PROJECTION DATE) > YEAR(NOW)

[FreemanZ · Accepted solution]

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

)

)

[Poojara_D12 · Accepted solution]

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

[yoa380]

I think I found the solution, hope it will be stable ....

I use filters of calculate and do +

V_TOTAL QTY_NEW =

VAR selectedpicture = SELECTEDVALUE(SELECT_PIC[ShortDate])

RETURN

CALCULATE(

    SUMX(PROJECTION, PROJECTION[TOTAL QTY]*PROJECTION[COST_WAC]), PROJECTION[PICTURE DATE]=selectedpicture, YEAR(PROJECTION DATE)<=YEAR(TODAY())) +

SUMX(PROJECTION, PROJECTION[TOTAL QTY]*PROJECTION[COST_BUD]), PROJECTION[PICTURE DATE]=selectedpicture, YEAR(PROJECTION DATE)>YEAR(TODAY()))

[yoa380]

Hello, thanks for quick reply. I cannot do that, first when I type IF( YEAR( ... it doesn't offer the choice to put PROJECTION DATE. It only shows measures from the table, not the other simple columns.

And if I force the formula with it, it will tell me : impossible to determine a unique value for the "projection date" column in the table. This can happen when a measurement formula refers to a column that contains many values, without specifying an aggregation such as min, max, count or sum to get a unique result.

[FreemanZ · Accepted solution]

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

)

)

[yoa380]

Yes, it seems to work thanks ! same result as my previous addition but shorter DAX