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

6 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    pwrbiadm add a new column using following expression:

     

    Sales = 
VAR __currentDate = Sales[Date]
VAR __previousDate = 
CALCULATE ( 
    LASTDATE ( Sales[Date] ), 
    Sales[Date] < __currentDate 
)
VAR __previousSales = 
CALCULATE ( 
    MAX ( Sales[Col B] ), 
    Sales[Date] = __previousDate 
)
RETURN
Sales[Col B] - __previousSales

     

    Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

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

      I am getting an error.

      "A date column containing duplicate dates was specified in the call to function 'LASTDATE'. This is not supported."

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    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
    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.