Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX sum with filter

Hi,    I have a dataset which looks like this: I basically need a DAX formula which add's the columns "Order gedaan picking" and "Orders openstaand picking" and filters it for a specific tim...
Need Help
  • ValtteriN's avatar
    ValtteriN
    3 years ago

    Hi,

    In that case, something like this should do:

    Measure 30 =
    var _cur =
    SUM('Table (24)'[Order gedaan picking])+SUM('Table (24)'[Orders openstaand picking]) //current time's value
    var _prevTime = CALCULATE(MAX('Table (24)'[Time]),ALL('Table (24)'[Time]),'Table (24)'[Time]<MAX('Table (24)'[Time])) //previous time
    var _prev = CALCULATE(SUM('Table (24)'[Order gedaan picking]),ALL('Table (24)'[Time]),'Table (24)'[Time]=_prevTime) + CALCULATE(SUM('Table (24)'[Orders openstaand picking]),ALL('Table (24)'[Time]),'Table (24)'[Time]=_prevTime) //previous time's values
    return
    _cur-_prev

     



Anonymous's avatar
Anonymous
Not applicable
3 years ago

ValtteriN 

 

Thank you for for time and efford and i understand what you mean, however to explain better what i need i'll show you what the outcome of this is. (Purple line)

 

 

This is incorrect as i would like to see the new orderlines that were generated during the intervals. For 12:30 the correct outcome should be the outcome of he current formule - the outcome of the previous timestamp, in this case 12:00. 

 

Hope you can help.

 

 

 

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi,

    In that case, something like this should do:

    Measure 30 =
    var _cur =
    SUM('Table (24)'[Order gedaan picking])+SUM('Table (24)'[Orders openstaand picking]) //current time's value
    var _prevTime = CALCULATE(MAX('Table (24)'[Time]),ALL('Table (24)'[Time]),'Table (24)'[Time]<MAX('Table (24)'[Time])) //previous time
    var _prev = CALCULATE(SUM('Table (24)'[Order gedaan picking]),ALL('Table (24)'[Time]),'Table (24)'[Time]=_prevTime) + CALCULATE(SUM('Table (24)'[Orders openstaand picking]),ALL('Table (24)'[Time]),'Table (24)'[Time]=_prevTime) //previous time's values
    return
    _cur-_prev

     



    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi ValtteriN 

       

      This is absolutely amazing, thank you very much!