Appending a Column with Yesterdays date

[rocky09]

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, LTP values will be over written with New values.

Is there a way to moves these values to a New Column and renaming the Column with Yesterday's Date and get the fresh data from the server. So, I will have every day's historical data.

I hope, I have explained well.

Thank you very much in advance.

[rocky09]

I was asked by my management to show comparision between Last week and present week. But, My probelm is, I am fetching data directly from Oracle Server. So, When I refresh the data, all the data will become latest.

Can anyone suggest me some good way to achieve this.  I am not sure whether there is a historical table existing in Oracle server or not.

[Phil_Seamark]

Hi @rocky09,

Do you have date columns in the tables you are collecting from Oracle?  

Eg, do you collect Sales data where there is a column that carries the datetime of the transaction?

If any of your tables have a Datetime column you can create measures in Power BI to show this over time as well as make comparisons.

If you aren't getting date info in your data then it makes it a little hard.

[rocky09]

@AnonymousThank you for your suggestion.

@Phil_SeamarkYes, the table has transaction date. Can you guide me, how can I use measures.?

[Phil_Seamark]

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]

@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]

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]

@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.

[Anonymous]

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]

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.

[Eric_Zhang]

---

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]

I have the same situation, but thankfully my report data volume is low where i just run the latest report every monday @6am and place the report (excel format) into a folder.  Within the report I included the field attribute "Report Ran Date" so it automatically populates a column with the date it was ran.  I then do the following:

1. Create a new PowerBI Query that is directed at the FOLDER that contains all the excel files

2. Make basic modification in PowerQuery

3. Create the new report using two main calculations:  

A: CUrrent Week data: CALCULATE(SUMMEASURE),LASTDATE(LOCATION OF REPORT DATE COLUMN))  The CALCULATE and LASTDATE formulas are required to use for every measure you make in order to extract out the current data from all the files. (LASTDATE is what finds the most current data)

B: Previous Week data calcs: =CALCULATE([SUMMEASURE],DATEADD(LASTDATE(LOCATION OF REPORT DATE COLUMN),-7,DAY))  WHat this does is it calculates the measure you create for the current values and it now calculates the previous week by looking at that report date column.  The LASTDATE plus -7, DAY gets you the previous week data

C: Now just make another measure called change which is CURRENT-PREVIOUS weeks measures.

Hope this helps you get started.