Forum Discussion

heramiah's avatar
heramiah
Frequent Visitor
9 years ago

calculation across rows

Hi, thanks in advance. First time on this forum.

 

In power bi i need to perform a calculation to work out date/time difference between Date1 and Date2. This calculation is to be ONLY performed where the ID number is a match to previous rows ID number. See example below. 

 

I am assuming i need to add a new measure (new column). I need help with the calculation/synntax

 

 

 

 

 

Regards,

 

Hera

 

Need Help

4 Replies

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    9 years ago

    heramiah First go to power bi desktop query editor and under Add Column tab click Add Index column and add index. Then Close&Apply and under Modelling tab create calculated column as below,

     

    Date Difference =
    'tableName'[date2Column] - IF(
    'tableName'[Index] = 0,
    'tableName'[date2Column],
    LOOKUPVALUE(
    'tableName'[date1Column],
    'tableName'[Index],
    'tableName'[Index]-1)
    )

    • heramiah's avatar
      heramiah
      Frequent Visitor
      9 years ago

      Many thanks for your reply.

       

      You are basing your calculation on the new Index column. However, i require my Index to be the ID column which is alpha numeric. 

       

      As shown in the example - where the same ID is repeated - require a calculated column to show the date difference. In other words, i am trying to work out if a person visits more than once - what the gap between their visits is in Hours.

      If an ID is only present once - then no calculation. The report will already be sorted in ascending order of ID and dates.

       

       

      You are very much there - just small tweaks. Please can you help again? Could possibly split the ID column as my actual ID's are Alpha Alpha, followed by numerics and so would just need to slip left by 2.

       

       

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        heramiah

        As shown in the example - where the same ID is repeated - require a calculated column to show the date difference. In other words, i am trying to work out if a person visits more than once - what the gap between their visits is in Hours.

        If an ID is only present once - then no calculation. The report will already be sorted in ascending order of ID and dates.

        In this scenario, you can firstly add an index column as ankitpatira mentioned above, then use VAR function to store the current Date1 value, and use SUMX function to calculate the date difference against the previous Date2(using index) when the same ID is repeated. See my sample below.

         

        I assume you have table called MyTestTable like below.

        1. Add an index column.

        2. Use the formula below to create a calculate column to show the date difference accordingly.

        Diff in hours = 
VAR vId = MyTestTable[ID]
VAR vIdex = MyTestTable[Index]
VAR vDate1 = MyTestTable[DATE1]
RETURN
    IF (
        COUNTROWS (
            CALCULATETABLE (
                MyTestTable,
                FILTER (
                    ALL ( MyTestTable ),
                    MyTestTable[ID] = vId
                        && MyTestTable[Index]
                        = vIdex - 1
                )
            )
        ) > 0,
        SUMX (
            FILTER (
                ALL ( MyTestTable ),
                MyTestTable[ID] = vId
                    && MyTestTable[Index]
                    = vIdex - 1
            ),
            DATEDIFF ( MyTestTable[DATE2], vDate1, HOUR )
        )
    )

         

        Regards