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
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

When you bring that data in, convert the column to be using just the DATE datatype.  Then connect that column to a separate Date Table where you will have just 1 row per date.  This will be the table that drives your "Time Intelligence".  Essectially this will unlock the functions like SAMEPERIODLASTYEAR, PARALLELPERIOD etc to build the measures you will likely need.

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.