How to remove cumulative frequency in power query or dax

[pwrbiadm]

Hi! I have the below table where the data gets generated every 1-2 seconds.

Column A - Datetime

Column B - Cumulative Counter

Column C - Calculated in Excel to remove cumulative frequency IF(B2>=B1,B2-B1,B2)

Please help me identify a way in power query or DAX to calculate Column C in power bi.

Thanks!

A	B	C
10/20/20 2:43	5.00	5.00
10/20/20 2:44	5.00	-
10/20/20 2:45	7.00	2.00
10/20/20 2:46	15.00	8.00
10/20/20 2:48	70.00	55.00
10/20/20 2:49	97.00	27.00
10/20/20 2:50	124.00	27.00
10/20/20 2:51	151.00	27.00
10/20/20 2:52	-	-
10/20/20 2:53	1.00	1.00
10/20/20 2:54	13.00	12.00
10/20/20 2:56	67.00	54.00

[pwrbiadm · Accepted solution]

I was able to use the below query for a dax calculated column which gave me the desired results without taking a long time to process the large dataset. Had to create an index column first in power query.

Column = var e = CALCULATE(MAX(Query4[B]),FILTER(Query4,Query4[Index]=EARLIER(Query4[Index])-1))

var f = Query4[B]

return IF(f=0,0,IF(f>=e,f-e,e))

[v-yuaj-msft]

Hi @pwrbiadm ,

Based on your description, you can create a new custom step in Power Query as follows.

 Custom1 = let 
tab = Table.AddIndexColumn(#"Changed Type","index"),
newtab = Table.AddColumn(tab,"New",(x)=>
let t1 = if List.Sum( Table.SelectRows(tab,(y)=>y[index]=x[index]-1)[Value])=null then 0 else List.Sum( Table.SelectRows(tab,(y)=>y[index]=x[index]-1)[Value]),
t2 = if x[Value]=null then 0 else x[Value]
in
if t2>=t1 
then t2-t1
else t2
)
in 
Table.SelectColumns(newtab,{"Value","New"})
in
Custom1

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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

[pwrbiadm]

Hi! I am not able to get the desired result. Please help with correcting the below. 

As a result, I want to see the columns:

("time", "BART_hmi_data.in_cnt_01.PV", "BART_hmi_data.in_cnt_02.PV", "BART_hmi_data.in_cnt_03.PV", "New")

Thanks for your time!

let
Source = OleDb.DataSource(**hidden**),
#"Removed Other Columns" = Table.SelectColumns(Source,{"tag", "time", "value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[tag]), "tag", "value", List.Sum),
#"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"BART_hmi_data.in_cnt_01.PV", Int64.Type}, {"BART_hmi_data.in_cnt_02.PV", Int64.Type}, {"BART_hmi_data.in_cnt_03.PV", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom1", each let
tab = Table.AddIndexColumn(#"Changed Type","index"),
newtab = Table.AddColumn(tab,"New",(x)=>
let t1 = if List.Sum( Table.SelectRows(tab,(y)=>y[index]=x[index]-1)[BART_hmi_data.in_cnt_01.PV])=null then 0 else List.Sum( Table.SelectRows(tab,(y)=>y[index]=x[index]-1)[BART_hmi_data.in_cnt_01.PV]),
t2 = if x[BART_hmi_data.in_cnt_01.PV]=null then 0 else x[BART_hmi_data.in_cnt_01.PV]
in
if t2>=t1
then t2-t1
else t2
)
in
Table.SelectColumns(newtab,{"BART_hmi_data.in_cnt_01.PV","New"}))
in
#"Added Custom"

[v-yuaj-msft]

Hi @pwrbiadm ,

Can you share some sample data and the expected result to have a clear understanding of your question?

You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.

Best Regards,

Yuna

[amitchandak]

@pwrbiadm , Create a new column like

new column =
var _max = maxx(filter(Table, [A] < earlier([A])),[A])
return
[B] - maxx(filter(Table, [A] =_max),[B])

[pwrbiadm]

@amitchandak Thanks for your reply. I tried but the query keeps on processing but wont execute. Maybe its coz of the large dataset. Can it be done in power query instead?