Forum Discussion

pwrbiadm's avatar
pwrbiadm
Icon for Helper I rankHelper I
5 years ago
Solved

Removing cumulative frequency using power(m) query

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...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    Hi pwrbiadm ,

     

    Create an index sorted by date time column.

    To refer to the previous row add a Custom Column. 

     

    try #"Added Index" [#" B "] {[Index] - 1} otherwise null

     

    Replace the "null" with 0 in previous column.

     

    Create the conditional column.

     

    if [#" B "] = null then 0 else if [#" B "] >= [Previous] then [#" B "] - [Previous] else [#" B "]

     

    Remove unnecessary columns.

     

    Sample.pbix

     

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

  • pwrbiadm's avatar
    pwrbiadm
    5 years ago
    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))

     

    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.

pwrbiadm's avatar
pwrbiadm
Icon for Helper I rankHelper I
5 years ago
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))

 

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.