The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Any idea, how to deduct time in previous row in the same column?
So, eg "row1-row2 = diff1", and etc... for whole column?
Hi @Anonymous ,
Go to "edit queries">"Add column">"Index Column">"From 1":
Then go back to data view>"new column">create 2 columns using dax expressions as below:
Column =
IF (
CALCULATE (
MAX ( 'Table'[Time] ),
FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) + 1 )
)
= BLANK (),
BLANK (),
CALCULATE (
MAX ( 'Table'[Time] ),
FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) + 1 )
)
)
Time diff =
VAR a =
DATE ( YEAR ( 'Table'[Time] ), MONTH ( 'Table'[Time] ), DAY ( 'Table'[Time] ) )
VAR b =
DATE ( YEAR ( 'Table'[Column] ), MONTH ( 'Table'[Column] ), DAY ( 'Table'[Column] ) )
VAR c =
IF ( b = BLANK (), 0, ABS ( VALUE ( a ) - VALUE ( b ) ) )
VAR a1 =
HOUR ( 'Table'[Time] ) * 3600
+ MINUTE ( 'Table'[Time] ) * 60
+ SECOND ( 'Table'[Time] )
VAR b1 =
HOUR ( 'Table'[Column] ) * 3600
+ MINUTE ( 'Table'[Column] ) * 60
+ SECOND ( 'Table'[Column] )
RETURN
IF ( b = BLANK (), 0, c * 24 * 3600 + ABS ( VALUE ( b1 ) - VALUE ( a1 ) ) )
And you will see :
Here,the time diff was caculated in seconds,if you hope in hour or minute ,pls make a change per your need.
For the related .pbix file,pls click here.
Best Regards,
Kelly
Is this time is descending order or can be assumed to ordered in desc order. Or do you have any primary key?
Try both solutions as new columns. In case you have primary use that in place of rank.
Ranktime = RANKX(table,table[starttime],,Desc)
diff = datediff(table[starttime],maxx(filter(table,table[Ranktime]=EARLIER(table[Ranktime])-1),table[starttime]),MINUTE)
or
diff = datediff(table[starttime],minx(filter(table,table[starttime]>EARLIER(table[starttime])-1),table[starttime]),MINUTE)
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
I like your second formula @amitchandak
diff = datediff(table[starttime],minx(filter(table,table[starttime]>EARLIER(table[starttime])-1),table[starttime]),MINUTE)
however it gives me error message:
function MINX has to many arguments, max is 2
Any advise?
Can you paste the formula you created? This seems correct. Also it is column
here we go @amitchandak
Try
Sloupec = datediff(CMA_RH[Column1.P1STARTTIME];minx(filter(CMA_RH;CMA_RH[Column1.P1STARTTIME]>(EARLIER(CMA_RH[Column1.P1STARTTIME])-1));CMA_RH[Column1.P1STARTTIME]);MINUTE)
@amitchandak formula works, However it doesn't give me good results.
my request:
5:33:50 - 5:22:00 = 110sec
This is wrong BI calculation
Remove -1 from formula
Sloupec = datediff(CMA_RH[Column1.P1STARTTIME];minx(filter(CMA_RH;CMA_RH[Column1.P1STARTTIME]>(EARLIER(CMA_RH[Column1.P1STARTTIME])));CMA_RH[Column1.P1STARTTIME]);MINUTE)
if still wrong , check what values you are getting for. Is it same as expcted
New column =minx(filter(CMA_RH;CMA_RH[Column1.P1STARTTIME]>(EARLIER(CMA_RH[Column1.P1STARTTIME])));CMA_RH[Column1.P1STARTTIME])
Still getting wrong difference
Any other suggestions?
Please share some sample Data.
Check, diff in seconds
https://www.dropbox.com/s/qqs3aovbo5wwwce/test%282%29.pbix?dl=0
Tried in two ways
Check, diff in seconds
https://www.dropbox.com/s/qqs3aovbo5wwwce/test%282%29.pbix?dl=0
you need to create one index column or you van refer below post
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
check my blog here
https://community.powerbi.com/t5/Community-Blog/Connecting-to-a-Tabular-Model-Using-Power-BI/ba-p/91...
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
81 | |
62 | |
54 |
User | Count |
---|---|
246 | |
119 | |
114 | |
87 | |
70 |