Help with DAX to add 1 more criterion to measure

[trdoan]

Hi everyone,

I have this measure:

% Change MoM in AVG TAT = 
DIVIDE(
 CALCULATE(
 AVERAGE('full updated ALL PARTS'[Final Run Time]), 
 FILTER('full updated ALL PARTS','full updated ALL PARTS'[Month Year]=MAX('full updated ALL PARTS'[Month Year])
 )
 ), 
 CALCULATE(
 AVERAGE('full updated ALL PARTS'[Final Run Time]),
 FILTER('full updated ALL PARTS','full updated ALL PARTS'[Month Year]=MIN('full updated ALL PARTS'[Month Year])))
,0) 
- 1

Can anyone please show me how I can add 1 more criterion to the above measure to get it response to another date slicer that is 'full updated ALL PARTS'[Posting Date]? (Posting Date is a column)

Calculated Column 'full updated ALL PARTS'[Month Year]:

Month Year = FORMAT([Posting Date],"mmmm yyyy")

I tried to add the logical operator OR but it returned error.

Is there any other way to nest 2 criteria in this measure to calculate % change MoM dynamically?

Thank you so much!

[v-piga-msft · Accepted solution]

Hi @trdoan,

What about changing your formula like below?

% Change MoM in AVG TAT =
DIVIDE (
    CALCULATE (
        AVERAGE ( 'full updated ALL PARTS'[Final Run Time] ),
        FILTER (
            ALLSELECTED ( 'full updated ALL PARTS' ),
            'full updated ALL PARTS'[Month Year]
                = MAX ( 'full updated ALL PARTS'[Month Year] )
        )
    ),
    CALCULATE (
        AVERAGE ( 'full updated ALL PARTS'[Final Run Time] ),
        FILTER (
            ALLSELECTED ( 'full updated ALL PARTS' ),
            'full updated ALL PARTS'[Month Year]
                = MIN ( 'full updated ALL PARTS'[Month Year] )
        )
    ),
    0
)
    - 1

If you still need help, please share some data sample which could reproduce your scenario and your desired output so that I can understand your scenario better and get the solution.

Best Regards,

Cherry

[trdoan]

Hi @v-piga-msft,

With the formula you corrected, do you know how I can switch it to only calculate % Change between 2 specific values?

Let's say I have a column in the same table called 'full updated ALL PARTS'[Vendor Name].

Is it possible to only calculate % change between Vendor A & Vendor B, ignoring all other vendors in that column?

Thanks a lot!

[trdoan]

Hi @v-piga-msft,

Thank you thank you thank you! It worked perfectly!