What is the best method for calculating min/max dates in a column?

[ClemFandango]

Hi there,

I am hoping someone can help.

I am trying to calculate the ‘min start date’ of ‘acc name’ in power query (as per table below).

I am using this in Power Query - List.Min(Table.SelectRows(#"Changed Type",(x)=>x[Acc Name]=[Acc Name])[Start Date]) but it takes absolutely ages (hours) - presumably because I have 90k+ rows.

I have also tried doing the same thing as New Column in DAX and it works much quicker:-

MinDate = CALCULATE (

        MIN ( Cust [Ent all.Start Date] ),

        ALLEXCEPT( Cust, Cust [Acc Name] )

    )

However, I want to calculate the MinDate in my calendar table as below, but neither calculated columns MinDate or MaxDate are available to use in the ‘Count’ calculated column. 

Count =

COUNTROWS (

    FILTER (

        'Ent all',

        [MMYYDD] <= 'Ent all'[MinDate]

            && [MMYYDD] < 'Ent all'[MaxDate]

    )

)

My final output should look something like this….

I am trying to count the rows where MMYYDD is inside the min & max dates, but neither calculated columns MinDate or MaxDate are available to use in the ‘Count’ calculated column of my date table. Am I doing something incredibly stupid?

Any help greatly appreciated.

Many thanks CF

[v-zhangti · Accepted solution]

Hi, @ClemFandango 

You can try the following methods.

Min Date = CALCULATE(MIN('Table'[Start Date]),ALLEXCEPT('Table','Table'[Acc Name]))

Max Date = CALCULATE(MAX('Table'[Start Date]),ALLEXCEPT('Table','Table'[Acc Name]))

Column:

Count = 
CALCULATE ( COUNT ( 'Table'[Acc Name] ),
    FILTER ( ALL ( 'Table' ),
        [Min Date] <= EARLIER ( 'Date'[MMYYDD] )
            && [Max Date] >= EARLIER ( 'Date'[MMYYDD] )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[ClemFandango]

Amazing stuff! Thank you v-zhangti

[ClemFandango]

Or to put it a bit simpler....

I am trying to achieve the following table

The ‘Count’ column should be calculated as below, but the ‘MinDate’ & ‘MaxDate’ calculated columns are in a different table, so I am unable to use them in my calculations.

Count =

COUNTROWS (

    FILTER (

        'Ent all',

        [MMYYDD] <= 'Ent all'[MinDate]

            && [MMYYDD] < 'Ent all'[MaxDate]

    )

)

Do you have any idea of a method that will allow me to use the mindate & maxdate in my calculations?

Any help greatly appreciated,

CF