How to find the Min by Month and Year

[Anonymous]

I am trying to find the Min Seq number by Month and Year.

Below I have a Matrix where I am finding the Min Sequence Number which is working at the monthly level.

For the month of Feb I want to see a Total of 6 which is correct. Also the Min for March is 8 which is also correct. 

However, I am also looking for the Min for the whole year while still showing the Months.

Below, I have a Min of 6 for the year. I would like to only see 6 and not see 8 when I expand for 2019

I tried to find the Min of total with no success. 

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

Table:

You may create a measure and a calculated column as follows.

calculated column:
Month Year = FORMAT('Table'[FROM_FILL_DATE],"mmm-yyyy")

Measure:
Min_Lead_Email = 
IF(
    ISINSCOPE('Table'[FROM_FILL_DATE].[Month]),
    CALCULATE(
            MIN('Table'[Value_Lead_Email]),
            FILTER(
                ALLSELECTED('Table'),
                MONTH('Table'[FROM_FILL_DATE]) = MONTH(MAX('Table'[FROM_FILL_DATE]))&&
                YEAR('Table'[FROM_FILL_DATE]) = YEAR(MAX('Table'[FROM_FILL_DATE]))
            )
    ),
    CALCULATE(
        MIN('Table'[Value_Lead_Email]),
        FILTER(
            ALLSELECTED('Table'),
            YEAR('Table'[FROM_FILL_DATE]) = YEAR(MAX('Table'[FROM_FILL_DATE]))
        )
    )
)

Result:

Best Regards

Allan

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

[parry2k]

@Anonymous share some sample data and will get you the solution.

[Anonymous]

I sent you a sample pbix file. Having challenges posting here.

This is a screen shot of what was sent.