4 Replies Latest reply: May 3, 2018 3:26 AM by Yari Marchetti RSS

    Monthly sum of last available date

    Yari Marchetti

      Hello,

      I got data that look like this:

       

      DATECOUNTRYCUSTOMER BASE DAILY
      2018-01-30US0
      2018-01-30AU1
      2018-01-31US1
      2018-01-31AU1
      2018-02-27US5
      2018-02-27AU1
      2018-02-28US5
      2018-02-28AU1
      2018-03-29US6
      2018-03-29AU1
      2018-03-30US7
      2018-03-30AU2
      2018-04-13US7
      2018-04-13AU3
      2018-04-15US8
      2018-04-15AU3

       

      and I'd like to create a table where the dimension is the month and measure is the sum of the customer base column of the most recent date of that month: for the past month it, usually, coincides with the last day of the month but for the current month it must show the most recent available values.

       

      So the table above should generate a table like this one:

       

      MONTHCUSTOMBER BASE
      2018-012
      2018-026
      2018-039
      2018-0411

       

      Could you help me with this?

       

      Thanks,

      Yari