Forum Discussion
Solution SageAppending 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, 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.
11 Replies
- 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.
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.
- Phil_Seamark
Microsoft Employee
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.?
