DAX command for MIN MAX in horometer

[Tarte]

Hi everyone,

I have a table containing meter readings for several machines:

Machine	Date	hour
A	01/07/2024	1000
A	10/07/2024	1100
A	23/07/2024	1150
B	09/07/2024	200
B	01/08/2024	300
B	20/08/2024	350

I would like a DAX measure that could find me the MAX and MIN of the meters for each month for the different machines:

Machine	Date	hour	MinMonth	MaxMonth
A	01/07/2024	1000	1000	1150
A	10/07/2024	1100	1000	1150
A	23/07/2024	1150	1000	1150
B	09/07/2024	200	200	200
B	05/08/2024	300	300	350
B	20/08/2024	350	300	350

I have tried to do this formula but due to the large number of rows in the starting table I have resource errors displaying the measure:

MinMonth =

CALCULATE(

    MIN(hour),

    FILTER(

        ALL(table),

        Machine = MAX(Machine) &&

        YEAR(Date) = YEAR(MAX(Date)) &&

        MONTH(Date) = MONTH(MAX(Date))

    )

)

thank you in advance

[Greg_Deckler · Accepted solution]

@Tarte What happens if you ditch the ALL in your filter clause?

Calculate = 
    VAR __Machine = MAX( 'Table'[Machine] )
    VAR __Date = MAX( 'Table'[Date] )
    VAR __Result = 
        CALCULATE(
            MAX( 'Table'[hour] ),
            'Table'[Machine] = __Machine, YEAR( 'Table'[Date] ) = YEAR( __Date ), MONTH( 'Table'[Date] ) = MONTH( __Date )
        )
RETURN
    __Result

Also, here is a No CALCULATE approach:

NC = 
    VAR __Machine = MAX( 'Table'[Machine] )
    VAR __Date = MAX( 'Table'[Date] )
    VAR __Table = FILTER( ALLSELECTED( 'Table' ), [Machine] = __Machine && YEAR( [Date] ) = YEAR( __Date ) && MONTH( [Date] ) = MONTH( __Date ) )
    VAR __Result = MAXX( __Table, [hour] )
RETURN
    __Result

[Anonymous · Accepted solution]

Hi @Tarte 

Thanks for the reply from Greg_Deckler.

The following measures are for your reference.

MinMonth = CALCULATE(MIN([hour]), ALLEXCEPT('Table', 'Table'[Machine]), MONTH('Table'[Date]) = MONTH(MAX([Date])))

MaxMonth = CALCULATE(MAX([hour]), ALLEXCEPT('Table', 'Table'[Machine]), MONTH('Table'[Date]) = MONTH(MAX([Date])))

Output:

Best Regards,
Yulia Xu

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

[Greg_Deckler · Accepted solution]

@Tarte What happens if you ditch the ALL in your filter clause?

Calculate = 
    VAR __Machine = MAX( 'Table'[Machine] )
    VAR __Date = MAX( 'Table'[Date] )
    VAR __Result = 
        CALCULATE(
            MAX( 'Table'[hour] ),
            'Table'[Machine] = __Machine, YEAR( 'Table'[Date] ) = YEAR( __Date ), MONTH( 'Table'[Date] ) = MONTH( __Date )
        )
RETURN
    __Result

Also, here is a No CALCULATE approach:

NC = 
    VAR __Machine = MAX( 'Table'[Machine] )
    VAR __Date = MAX( 'Table'[Date] )
    VAR __Table = FILTER( ALLSELECTED( 'Table' ), [Machine] = __Machine && YEAR( [Date] ) = YEAR( __Date ) && MONTH( [Date] ) = MONTH( __Date ) )
    VAR __Result = MAXX( __Table, [hour] )
RETURN
    __Result