Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to compare data current vs previous in a dynamic table without time period?

Hi,

 

As a newbie to pbi & dax basics, i need help to create a formula comparing current data vs previous data with specific filters applied (i.e. a customer's last order vs previous order with same filters like from/to location and even commodity type). Dataset will be updated daily, not real-time query.  Any suggestion? 

 

Thanks, 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Anonymous 
    The [Value] is the data field(e.g. sales) you want to compare, for example if you want to compare the sum sales between current order date and previous order date:

     

    The measures should be something like the following.

    Previous data = Sumx(filter(allselected(IHRACAT), [NEW CUSTOMER] = max([NEW CUSTOMER]) && [OrderDate] < max([OrderDate])),[Data])))
Current data = Sumx(filter(allselected(IHRACAT), [NEW CUSTOMER] = max([NEW CUSTOMER]) && [OrderDate] = max([OrderDate])))

    Then you can just create a measure to compare:
    Measure = current data - previous data

     

    Paul Zheng _ Community Support Team
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

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

    Anonymous , A new column like this

    New column =
    var _last = maxx(filter(Table, [Customer_id] = earlier([Customer_id]) && [Order] < earlier([Order])),[Order])

    return
    sumx(filter(Table, [Customer_id] = earlier([Customer_id]) && [Order] _last),[value])

     

    or measure like

     

    New Measure =
    var _last = maxx(filter(allselcted(Table), [Customer_id] = max([Customer_id]) && [Order] < max([Order])),[Order])

    return
    sumx(filter(allselcted(Table), [Customer_id] = max([Customer_id]) && [Order] _last),[value])

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you for your answer but i probably do sth wrong, in your measure syntax "_last" raises error as unexpected expression, 

      Here's my version after putting tables in your measure; 

       

      New Measure = var _last = maxx(filter(allselected(IHRACAT), [NEW CUSTOMER] = max([NEW CUSTOMER]) && [TEU] < max([TEU])),[TEU])

      return

      sumx(filter(allselected(IHRACAT), [NEW CUSTOMER] = max([NEW CUSTOMER]) && [TEU] _last),[TEU])

       

      *i assume that you set this measure up considering unique order IDs, but in my dataset there's no order IDs, instead i should use order dates to compare last vs previous order. 

      *one minor thing, at the end of your measure, what should be in "value" table, i'm a bit confused while selecting right table for it. 

      Thanks again for your help

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Anonymous 
        The [Value] is the data field(e.g. sales) you want to compare, for example if you want to compare the sum sales between current order date and previous order date:

         

        The measures should be something like the following.

        Previous data = Sumx(filter(allselected(IHRACAT), [NEW CUSTOMER] = max([NEW CUSTOMER]) && [OrderDate] < max([OrderDate])),[Data])))
Current data = Sumx(filter(allselected(IHRACAT), [NEW CUSTOMER] = max([NEW CUSTOMER]) && [OrderDate] = max([OrderDate])))

        Then you can just create a measure to compare:
        Measure = current data - previous data

         

        Paul Zheng _ Community Support Team
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.