Sum column between two dates in a measure

[Anonymous]

Hi,

I want to create a measure to sum a column between two dates.

I have a column (ColumnDaysCount) that does the same thing but I heard that having measures instead of columns is better.

I tried to copy/paste the formulae from my column to a new measure, which did not work:

DaysSum := 
CALCULATE (
    COUNT ( DimDate[Date] );
    FILTER (
        DimDate;
        DimDate[Date] >= 'Table'[Opened]
            && DimDate[Date] <= 'Table'[Closed]
            && DimDate[Workday] = 1
    )
)

I get the error that the measure cannot make a reference to a single value from ’Table’[Opened] and ’Table’[Closed]; I understand it.

Then I saw this from a measure developped by someone before me and it was giving the same result as the column ColumnDaysCount :

DaysSum := 
SUMX (
    FILTER (
        DimDate;
        DimDate[Date] >= MIN( 'Table'[Opened] )
            && DimDate[Date] <= MAX( 'Table'[Closed] )
            && DimDate[Workday] = 1
    );
    DimDate[Workday]
)

Why does the MIN() and MAX() resolve the problem? Does the measure sum the result for all rows or does it calculates the sum for every ID from the moment I put it in the visualisation?

Can you give me keys to understand this?

Thank you,

[Nathaniel_C]

Hi @Anonymous ,

My understanding is this: When you have the DAX in a calculated column, it knows where it is and what to do using row context.  However when you have it in a measure there is no row context.  The MIN() and MAX () and some other aggregators give the DAX the position (at the top or the bottom, if you will) of the column.  Thus it works even if it is in a card.  However, written correctly it also works when you filter it on a matrix or table.  Pretty amazing!

Not sure if that was the best explanation...anyone else?

Nathaniel