Needs historic data + refresh data

[NickProp28]

Dear Community, 

I have been creating a report which needs to show the whole status for the products.

For example raw data during the week 1.

During the week 2, after I refresh the raw data.

What I need to show in BI report is in this way,

I have did some research (https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/) how to store the historical data.  

But its doesnt help, because I retrieve my data from database, there have huge data also with new product coming in. The 'old data' mentioned in the blog have to be refresh weekly and cannot disable the option "Include in report refresh".

Thanks for the attention. Any help provided will greatly appreciated!

[v-henryk-mstf]

Hi @NickProp28 ,

The incrementally refreshed data is added in the form of rows on the basis of historical data. According to your description, even if the refreshed status is changed, it will only replace the original status, and will not be updated to the form of "Pending>Approved" in the field.

If you can create a column in the database to display the latest status, you can use the CONCATENATE function in powerbi to display this status:

Col =
IF (
    [Status] = [Up_status],
    "Pending",
    CONCATENATE ( [Status] & ">", [Up_status] )
)

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NickProp28]

Dear @v-henryk-mstf ,

Thanks for your replied. 

But my main problem is, how to store the historical data when I perform the weekly refresh on the raw data.

Lets say I have one table with only 2 column. (Product and status)

Example in week 1, two product is still under 'pending' status.

In week 2, after I refresh my raw data. The chair has been approved. 

With your CONCATENATE function, I can show Chair status from Pending > Approved. 

But In week 3, when I refresh my raw data. The historic data for chair has been overwritten to Approved. 

So its only result "Approved" in chair status .

So would like to ask, isit anyway I can still keep my "Pending" status no matter how many time I refresh the raw data. Appreciated any help you may provided. 

[v-henryk-mstf · Accepted solution]

Hi @NickProp28 ,

According to your description, similar results may not be achieved in powerbi. In powerbi, the best way is to continuously update the status results and keep records in the form of rows. Then create a column and use the CONCATENATEX function to show the entire change process in the card visual. If it is only updated in the original row, it will not be saved.

just like:

Col = 
CALCULATE (
    CONCATENATEX (  'Table','Table'[Status]  , "->" ),
    ALLEXCEPT ( 'Table', 'Table'[Product] )
)

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Calvin69]

Hi @NickProp28 ,

What is your original data source location? Is it a manually controlled excel sheet or is it a system table of some kind?

Can you provide more details please?

H

[NickProp28]

Dear @Calvin69 ,

Thank for your attention.

I will retrieved the data from database.
And is only have these two column, and I would like to store the historic data when I perform the weekly updates.

[Calvin69]

Hi @NickProp28 ,

I am not 100% sure, perhaps someone else can assist further with your query however, I recently had a similar situation with a data set that was located on Sharepoint and I was able to activate the historical records and pull out the historical entries to work with in PBI.

I would suggest that you query the data at source to generate a consolidated table entries before importing it into Power BI.

Good luck

H