Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculation over rows with condition

Hello all.   I am trying to improve a file I have worked on. I need some calculations over rows with a condition, and I was not able to do them on the interface (DAX), so I created different tables...
General Comment
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi Anonymous,

     

    If you want to get the summaried stop time you can try to add new measure with below formula:

    Stop time(Summaried) =
VAR temp =
    ADDCOLUMNS (
        ALL ( Trips ),
        "Stoped Time", LOOKUPVALUE (
            Trips[TripEndDateTime],
            Trips[VehicleID], [VehicleID],
            Trips[Date], [Date],
            Trips[Index], [Index] - 1
        )
    )
RETURN
    SUMX (
        ADDCOLUMNS (
            FILTER ( temp, [VehicleID] = MAX ( [VehicleID] ) && [Date] = MAX ( [Date] ) ),
            "Diff", DATEDIFF ( [Stoped Time], [TripStartDateTime], SECOND )
        ),
        [Diff]
    )

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Anonymous,

 

Based on your description, it sounds like compare current records and previous records.

Can you please share some sample data for further test and analysis?

 

You can also take a look at below link which has the similar requirement:

Recording Daily Data

 

Regards,

Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Anonymous

 

Sure, here it is:

 

Sample

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Anonymous,

     

    Can you please share pbix/excel file with some fake data? I will test on it.(it is trouble to manual input your data from the screenshots)

     

    Regards,

    Xiaoxin Sheng

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi Anonymous,

         

        You can take a look at below formulas if they suitable for your requirement.

         

        Measures:

        Number of Stop = 
CALCULATE (
    COUNT ( [VehicleID] ) - 1,
    FILTER (
        ALLSELECTED ( 'Trips' ),
        [VehicleID] = MAX ( [VehicleID] )
            && [TripStartDate] = MAX ( [TripStartDate] )
            && [Date] = MAX ( [Date] )
    )
)

Stop time = 
var _rangeEnd=MAX(Trips[TripStartDateTime])
VAR started =
    CALCULATE (
        MAX ( [TripEndDateTime] ),
        FILTER (
            ALL ( Trips ),
            [VehicleID] = MAX( [VehicleID] )
                && [Date] = MAX ( [Date] )
                && [TripEndDateTime] < _rangeEnd
        )
    )
RETURN
    DATEDIFF ( started,_rangeEnd, SECOND ) + 0

Return time = 
VAR _lastStart =
    CALCULATE (
        MAX ( Trips[TripStartDateTime] ),
        FILTER (
            ALLSELECTED ( Trips ),
            [VehicleID] = MAX ( [VehicleID] )
                && [Date] = MAX ( Trips[Date] )
        )
    )
VAR _lastEnd =
    CALCULATE (
        MAX ( Trips[TripEndDateTime] ),
        FILTER (
            ALLSELECTED ( Trips ),
            [VehicleID] = MAX ( [VehicleID] )
                && [Date] = MAX ( Trips[Date] )
        )
    )
RETURN
    IF (
        DATEVALUE ( _lastStart ) <> DATEVALUE ( _lastEnd ),
        DATEDIFF ( _lastStart, _lastEnd, SECOND ),
        DATEDIFF ( _lastEnd, DATEVALUE ( _lastEnd ) + 1, SECOND )
    )

         

        Regards,

        Xiaoxin Sheng