Forum Discussion

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

Displaying previous value instead of blank

I have 3 tables: Products Product quantity by date Calendar On my report I have a date slicer from calendar where I can select a single date. Then I have a table visual that displays ALL produc...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi JKoivu 

     

    You can use a measure to get the result.

    Previous quantity = 
VAR __previousDate =
    CALCULATE (
        MAX ( 'Quantity by date'[Date] ),
        FILTER (
            ALL ( 'Quantity by date' ),
            'Quantity by date'[Date] < MAX ( 'Calendar'[Date] )
                && 'Quantity by date'[Product ID] = MAX ( 'Products'[Id] )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Quantity by date'[Quantity] ),
        FILTER (
            ALL ( 'Quantity by date' ),
            'Quantity by date'[Date] = __previousDate
                && 'Quantity by date'[Product ID] = SELECTEDVALUE ( 'Products'[Id] )
        )
    )

     

    If you calculate previous quantity in a calculated column, you should also use a measure to get the corresponding previous quantity column value when you hope to use a slicer to switch it.

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

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

    CNENFRNL Thanks for your reply, this works great on this example! Though I forgot to mention that there can be gaps of several days, so the previous quantity could be from many days ago, not just previous. Any tips on how to achieve that?

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

      I still haven't figured this out, can anyone help me?