Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate minimum value over Last 12 Months

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
1102.00
2110.05
350.05
430.00
525.10
670.00
790.00
8110.00
945.00
1065.00
1185.00
12120.00

 

I have tried below dax : 

MINX(VALUES('Date'[Month]), [Amount])
However, this dax giving me result as 0.00 everytime. 
 
Thanks in Advance.

 

 

Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    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

8 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks Jimmy, this expression works perfectly fine with me.

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        6 years ago

        Hello Anonymous 

         

        your feedback is very appreciated

        I'm very glad I could help you

         

        All the best

         

        Jimmy

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

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