Forum Discussion

itsmeanuj's avatar
itsmeanuj
Helper IV
3 years ago
Solved

Max value based on dynamic date filter

I need to calculate something similar to the "partition by" function in SQL.    My data contains multiple instances of IDs. I need to create a measure that would pick up the latest value of the "Ca...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  itsmeanuj ,

     

    Here are the steps you can follow：

    1. Create measure.

    Measure =
var _maxdatecreateon=
MAXX(
    FILTER(ALLSELECTED('Table'),
    'Table'[PRODUCT_NAME]=MAX('Table'[PRODUCT_NAME])&&'Table'[ID]=MAX('Table'[ID])),[CHANGED_ON])
var _maxstamp=
MAXX(
    FILTER(ALLSELECTED('Table'),     'Table'[PRODUCT_NAME]=MAX('Table'[PRODUCT_NAME])&&'Table'[ID]=MAX('Table'[ID])&&'Table'[CHANGED_ON]=_maxdatecreateon),'Table'[STAMP])
return
MAXX(
    FILTER(ALL('Table'),    'Table'[PRODUCT_NAME]=MAX('Table'[PRODUCT_NAME])&&'Table'[ID]=MAX('Table'[ID])&&'Table'[CHANGED_ON]=_maxdatecreateon&&'Table'[STAMP]=_maxstamp),'Table'[CALL_FLAG])
    count of IDs =
CALCULATE(
    DISTINCTCOUNT(
    'Table'[ID]),FILTER(ALL('Table'),'Table'[PRODUCT_NAME]=MAX('Table'[PRODUCT_NAME])))

    2. Result:

     

    Best Regards,

    Liu Yang

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

  • itsmeanuj's avatar
    itsmeanuj
    Helper IV
    3 years ago

    amitchandak - Thanks for your response. But in my scenario, we first need to take latest "changed_on" date and then latest "Stamp" date in that "Changed_on" date. Also this needs to be dynamic as there is a "changed_on" date slicer at the top of the page.