Forum Discussion

LorenzoTr's avatar
LorenzoTr
Frequent Visitor
2 years ago
Solved

Difference between values in a day and incremental column

Hi All,

I would calculate the difference between values in a day and relative column that shows that increment of the day (values at 12AM should be 0).

Here the tab with columns "Diff" and "Increm" that are what I need.

Thanks!

Lorenzo

 

Timestamp_ConvertCount_HDiffIncrem
16/5/24 1.0020--
16/5/24 2.002222
16/5/24 3.002535
16/5/24 4.0032712
16/5/24 5.0033113
16/5/24 6.0033013
16/5/24 7.0035215
16/5/24 8.0036116
16/5/24 9.0039319
16/5/24 10.0041221
16/5/24 11.0043223
16/5/24 12.0044124
16/5/24 13.0045125
16/5/24 14.0047227
16/5/24 15.0050330
16/5/24 16.0054434
16/5/24 17.0056236
16/5/24 18.0057137
16/5/24 19.0060340
16/5/24 20.0062242
16/5/24 21.0063143
16/5/24 22.0064144
16/5/24 23.0066246
17/5/24 0.006710
19/5/24 1.006811
20/5/24 2.007245
21/5/24 3.007316
22/5/24 4.0079612
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi LorenzoTr ,

    You want to make sure that your date column is in the correct time format, such as "YYYY-MM-DD HH:MM:SS .

    My diff's DAX has the EARLIER function, which is used in the calculated column, so be careful to recognize it.

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

     

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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi LorenzoTr ,
    Thank you fahadqadir3  and amitchandak   very much for the solution, and I've tried other ways to help you understand the problem:

    These are the two solutions I wrote in the case of accomplishing your first goal, one in MEASUREMENT and one in CALCULATED COLUMNS.
    diff is the solution to your second need.

    Column = 
    VAR now_='Table'[Count_H]
    VAR pervious=
    CALCULATE(MAX('Table'[Count_H]),FILTER('Table',
    [Timestamp_Convert]<EARLIER([Timestamp_Convert])
        )
    )
    RETURN 
    IF(pervious =BLANK(),
    BLANK(),now_-pervious)
    diff 1 = 
     VAR currenttime = MAX('Table'[Timestamp_Convert])
     VAR previoustime=CALCULATE(MAX('Table'[Timestamp_Convert]),FILTER(ALLSELECTED('Table'),'Table'[Timestamp_Convert]<currenttime)
     )
     VAR previousvalue=CALCULATE(MAX('Table'[Count_H]),FILTER(ALLSELECTED('Table'),'Table'[Timestamp_Convert]=previoustime)
     )
     RETURN
     IF( currenttime=MINX(ALL('Table'),'Table'[Timestamp_Convert]),
     0,
     MAX('Table'[Count_H])-previousvalue)
    
    diff = 
    VAR restTime =TIME(24, 0, 0)
    VAR currtime =TIME(HOUR('Table'[Timestamp_Convert]),MINUTE('Table'[Timestamp_Convert]),SECOND('Table'[Timestamp_Convert]))
    VAR currentcount='Table'[Count_H]
    VAR previouscount=CALCULATE(SUM('Table'[Column]),FILTER('Table','Table'[Timestamp_Convert]<=EARLIER('Table'[Timestamp_Convert])&&YEAR('Table'[Timestamp_Convert])=YEAR(EARLIER('Table'[Timestamp_Convert]))
    )
    )
    RETURN
    IF( currtime=restTime,
    0,
    previouscount
    )

     

    I just that you are encountering the error because you are using earlier in DAX in measure, the place where earlier should be used is in calculated columns and virtual tables, the following URL may solve your query.

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

     

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

     
    • LorenzoTr's avatar
      LorenzoTr
      Frequent Visitor

      Thanks, Diff1 is working well!

      About diff1, I tried to enter it as a measure but I find some issues with formulas (HOUR, MINUTE, SECONDS, EARLIER) cause it's not possible to enter column (ex: 

      HOUR('Table'[Timestamp_Convert]

      Do you've any suggestion?

      • Anonymous's avatar
        Anonymous
        Not applicable

        Hi LorenzoTr ,

        You want to make sure that your date column is in the correct time format, such as "YYYY-MM-DD HH:MM:SS .

        My diff's DAX has the EARLIER function, which is used in the calculated column, so be careful to recognize it.

         

        Hope it helps!

         

        Best regards,
        Community Support Team_ Tom Shen

         

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

  • LorenzoTr , Two New columns

     

    Create an additional Date column in Table

    Date = datevalue([Timestamp_Convert])

     

    and then

     

    Diff with last value =
    var _min = Minx(filter(Table, [Date]= Earlier([Date])), [Timestamp_Convert])
    return
    Minx(filter(Table, [Date]= Earlier([Date]) && [Timestamp_Convert] = _min), [Count_H] ) - [Count_H]

  • fahadqadir3's avatar
    fahadqadir3
    Icon for Solution Supplier rankSolution Supplier
    Differsa =

    VAR CurrentRow = 'Table'[Count_H]

    VAR PreviousRow =

        CALCULATE(

            MAX('Table'[Count_H]),

            FILTER(

                ALL('Table'),

                'Table'[Timestamp_Convert] < EARLIER('Table'[Timestamp_Convert])

            )

        )

    RETURN

    IF(ISBLANK(PreviousRow), BLANK(), CurrentRow - PreviousRow)
    Increment = 
    VAR CurrentTimestamp = 'Table'[Timestamp_Convert]
    VAR StartOfDay =
    CALCULATE(
    MIN('Table'[Timestamp_Convert]),
    FILTER(
    'Table',
    FORMAT('Table'[Timestamp_Convert], "yyyy-MM-dd") = FORMAT(CurrentTimestamp, "yyyy-MM-dd")
    )
    )
    RETURN
    IF(
    'Table'[Count_H] = CALCULATE(MIN('Table'[Count_H]), 'Table'[Timestamp_Convert] = StartOfDay),
    0,
    CALCULATE(
    SUMX(
    FILTER(
    'Table',
    'Table'[Timestamp_Convert] <= CurrentTimestamp &&
    FORMAT('Table'[Timestamp_Convert], "yyyy-MM-dd") = FORMAT(CurrentTimestamp, "yyyy-MM-dd")
    ),
    'Table'[Differsa] // Use 'Differsa' as it's the name of your calculated column
    ),
    FILTER(
    ALL('Table'),
    'Table'[Timestamp_Convert] <= CurrentTimestamp &&
    FORMAT('Table'[Timestamp_Convert], "yyyy-MM-dd") = FORMAT(CurrentTimestamp, "yyyy-MM-dd")
    )
    )
    )

    LorenzoTr  Hope it works.

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!! 

    • LorenzoTr's avatar
      LorenzoTr
      Frequent Visitor

      Thanks for the answer. Cannot undertand why I've an error when I enter DAX function EARLIER.

      Is there any alternative to this? thanks