Removing cumulative frequency using power(m) query

[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 using power(m) query to calculate Column C in power bi.

Due to the large dataset (over 3 million records), the dax query keeps on processing but won't execute.

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

[Icey]

Hi @pwrbiadm ,

Due to your large dataset, it is suggested to create a measure like so:

Measure =
VAR ThisRow_B =
    MAX ( [B] )
VAR LastRow_B =
    CALCULATE (
        MAX ( 'Query1 (2)'[B] ),
        FILTER (
            ALLSELECTED ( 'Query1 (2)' ),
            'Query1 (2)'[Index]
                = MAX ( 'Query1 (2)'[Index] ) - 1
        )
    )
RETURN
    SWITCH ( TRUE (), ThisRow_B >= LastRow_B, ThisRow_B - LastRow_B, ThisRow_B )

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[AlB]

Hi @pwrbiadm 

This might run faster in DAX. However, place the following M code in a blank query to see the steps. If it's not fast enough we'll try another approach

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZFLDsAgCAWvYlibFlC0ehXj/a/RNl01fZiUsCIzhM8YJLzrnUF7ThQpOGEbM834FvJfwRZCRULxBIH9Dw+vjPDm4Q0NY4xx0YxocWgTROtFf4qrh8Au7kMkIdy9bnn2nyc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type number}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"B"}),
   
    col_ = List.RemoveLastN({0} & #"Replaced Value1"[B], 1),
    res_ = Table.FromColumns(Table.ToColumns(#"Replaced Value1") &  {col_}, {"A", "B","B_2"}),
    #"Changed Type1" = Table.TransformColumnTypes(res_,{{"B_2", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [B]-[B_2], type number),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom",null,0,Replacer.ReplaceValue,{"B_2"})
in
    #"Replaced Value"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers