Calculate minimum value over Last 12 Months

[Anonymous]

Hi Experts,

I need to find out the Minimum amount over last 12 Months, below is my sample data. 

Expected output should be : 25.10 which is in Month 5. 

Month	Amount
1	102.00
2	110.05
3	50.05
4	30.00
5	25.10
6	70.00
7	90.00
8	110.00
9	45.00
10	65.00
11	85.00
12	120.00

I have tried below dax : 

MINX(VALUES('Date'[Month]), [Amount])

However, this dax giving me result as 0.00 everytime. 

Thanks in Advance.

[Jimmy801 · Accepted solution]

Hello @Anonymous 

you can't reference a measure in a Min formula. Therefore you have to recalculate you sumamount in your new min-measure using some Summarize and addcolum. Here the solution that should work for you

MinOfSumAmount = calculate(
    minx(
        ADDCOLUMNS(
            SUMMARIZE(
                'Table';
                'Table'[Month]
            );
            "sum";
            CALCULATE(
                sum(
                    'Table'[Amount]
                );
                filter(
                    all(
                        'Table'[Month]
                    );
                    'Table'[Month]=earlier(
                        'Table'[Month]
                    )
                )
            )
        )
        ;[sum]
    );
    all('Table')
)

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Thanks Jimmy, this expression works perfectly fine with me.

[Jimmy801]

Hello @Anonymous 

your feedback is very appreciated

I'm very glad I could help you

All the best

Jimmy

[Anonymous]

Hi,

you have to remove the Date[Month] Context with the ALL Statement and you will get the exact result

MinTest = Calculate(MIN([Total Amount]);ALL('Date'[Month]))

[Anonymous]

Thanks for the reply.

Amount field is calculated measure so i cannot use Min function on it, i have updated your suggested expression and tried  as below one but still it gives 0:

Calculate(MINX(VALUES(Date[Month]),[Amount]),ALL('Date'[Month]))

Thanks.

[Anonymous]

Thanks, but i have getting below error for the  expression:

MinTest = Calculate(MIN([Total Amount]);ALL('Date'[Month]))

Errror : MIN function only accepts a column reference as the argument number 1

[dax]

Hi @Anonymous , 

You could try to use below measure to see whether it work or not

Measure = MINX(ALL('Date'[Month]),[Total Amount])

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

In my formula I also use a measure for the amount value. Please try the formula and with your amount measure