Forum Discussion

rocky09's avatar
rocky09
Icon for Solution Sage rankSolution Sage
9 years ago

Appending a Column with Yesterdays date

The below is an example of my table. This is basically a stock table (taken for testing purpose). If you look into the last Column LTP i.e today's data.  Generally, When I refresh the table tomorrow,...
Need Help
rocky09's avatar
rocky09
Icon for Solution Sage rankSolution Sage
9 years ago

Phil_Seamark

 

Then connect that column to a separate Date Table where you will have just 1 row per date.

 

Thank you. I am not getting it. Do I need to duplicate the Date Table with all the information from the Original Table. Sorry for my poor understanding.

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi rocky09,

 

The separate date table can have just one column as a bare minimum.  But you can add columns to bucket dates into Months, Weeks, or Quarters etc if you want.  The main thing is to have a Date/Calendar table that has just 1 row per date (and no more).

 

You don't need to duplicate your date table with the information from your original table.

 

Sounds like you are making good progress.

  • rocky09's avatar
    rocky09
    Icon for Solution Sage rankSolution Sage
    9 years ago

    Phil_Seamark

     

    Still puzzling.. Sorry. :)

     

    I am getting data directly from Oracle Server. So, every time, I refreshes the table , the previous data will be overwritten. Hence, I cannot have previous data to compare this week.

     

    If I create a new table as you mentioned, How it will helpful in comparing. Please adivise. Sorry for not providing clear details before.

    • Eric_Zhang's avatar
      Eric_Zhang
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      AFAIK, there's no such "HISTORIC" feature in Power BI, maybe you can follow this workaround.

      For me, I'd prefer to maintain a history table in Oracle database end. If it is not possible, maybe set up an ETL process to extract the data from Oracle to anywhere for archieve purpose so that you can always find the last week, the week before last week etc.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Rocky,

      I think you are over complicating this.  The roadblock you are running into is ASSIGNING a time stamp to your data extract from Oracle.  When you run your report, is it possible to have Oracle add another column which includes the date that you ran the report? This is pretty common in most reports.  So the screenshot you posted had 3 columns.  Ideally you would have 4 where the last is the date that the report was ran.  Once you can get this, the rest will be easy, you wont even need a seperate "Date table."

      • rocky09's avatar
        rocky09
        Icon for Solution Sage rankSolution Sage
        9 years ago

        Thank you very much for your inputs.  After lot of brainstorming. I finally came to a decision to make MS Access is the data source. In my Access, I can easily play with queries to get what I needed.