cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NickProp28
Post Partisan
Post Partisan

Needs historic data + refresh data

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.

NickProp28_0-1630051533205.png

 

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

NickProp28_1-1630051656315.png

 

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

NickProp28_2-1630051779102.png

 

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!

1 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] )
)

vhenrykmstf_0-1630481126449.png

 


Best Regards,
Henry


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

View solution in original post

6 REPLIES 6
v-henryk-mstf
Community Support
Community Support

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

vhenrykmstf_0-1630313380662.png

 


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.

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.

NickProp28_0-1630314202076.png

 

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

NickProp28_1-1630314364067.png

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. 

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

vhenrykmstf_0-1630481126449.png

 


Best Regards,
Henry


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

View solution in original post

Calvin69
Helper II
Helper II

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

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.

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!