how to compare two rows in column and return value in DAX calculated column

[WTAS80486]

I am using this to calculate cumulative runhours

Rolling Runhours = CALCULATE (

                SUM ( 'MeterReading'[ Run Hours]),

                FILTER (

                    ALL ( 'MeterReading'),

                    'MeterReading'[Asset PK ID] = EARLIER ( 'MeterReading'[Asset PK ID]  )

                        && 'MeterReading'[readingdate]<= EARLIER ('MeterReading'[readingdate])

                )

            )

But this returns

same value for same dates. I want to have 0 in first row for 11/9 and 265 in second row for 11/9

How can i achieve that?

[Anonymous · Accepted solution]

Hi @WTAS80486 ,

I have create a simple sample, please refer to it to see if it helps you.

Add an index column first.

Then create a measure.

Measure =
VAR _count =
    CALCULATE (
        COUNT ( MeterReading[readingdate] ),
        FILTER (
            ALL ( MeterReading ),
            MeterReading[readingdate] = SELECTEDVALUE ( MeterReading[readingdate] )
        )
    )
VAR _1mindate =
    CALCULATE (
        MIN ( MeterReading[Index] ),
        FILTER (
            ALL ( MeterReading ),
            MeterReading[readingdate] = SELECTEDVALUE ( MeterReading[readingdate] )
        )
    )
VAR _result =
    CALCULATE (
        SUM ( MeterReading[Run Hours] ),
        FILTER (
            ALL ( MeterReading ),
            MeterReading[readingdate] <= SELECTEDVALUE ( MeterReading[readingdate] )
        )
    )
RETURN
    IF ( _count <> 1 && _1mindate = MAX ( MeterReading[Index] ), 0, _result )

or a column.

Column =
VAR _count =
    CALCULATE (
        COUNT ( MeterReading[readingdate] ),
        FILTER (
            ALL ( MeterReading ),
            MeterReading[readingdate] = EARLIER ( MeterReading[readingdate] )
        )
    )
VAR _1mindate =
    CALCULATE (
        MIN ( MeterReading[Index] ),
        FILTER (
            ALL ( MeterReading ),
            MeterReading[readingdate] = EARLIER ( MeterReading[readingdate] )
        )
    )
VAR _result =
    CALCULATE (
        SUM ( MeterReading[Run Hours] ),
        FILTER (
            ALL ( MeterReading ),
            MeterReading[readingdate] <= EARLIER ( MeterReading[readingdate] )
        )
    )
RETURN
    IF ( _count <> 1 && _1mindate = ( MeterReading[Index] ), 0, _result )

If I have misunderstood your meaning, please provide a pbix file without privacy information and more details with your desired output.

Best Regards

Community Support Team _ Polly

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

[WTAS80486]

@Anonymous Thanks

[Kriekis]

1. You can add Index column

2. Then You add readingdate_v2

3. And finally Your [Rolling Runhours] think works fine

This solution works fine if You don't get exactly the same runhours in the same day multiple times.

If this is a case then adding additional Index column in Power Query can help here.