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 1 102.00 2 110...
  • Jimmy801's avatar
    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