Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
LorenzoTr
Frequent Visitor

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
1 ACCEPTED SOLUTION
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.

View solution in original post

6 REPLIES 6
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
)

vxingshenmsft_0-1719979106924.png

 

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.

 

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
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.

fahadqadir3
Super User
Super User

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")
)
)
)
fahadqadir3_0-1719913298127.png

@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!! 

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

Is there any alternative to this? thanks

 

amitchandak
Super User
Super User

@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]

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors