Form Table with Historical Data

[fedeleu]

Nice day

Every day I receive a .txt file with computers that need to be repaired. As relevant data is the date of entry, date of the report and ID_equipo. New revenue can be added every day, but if a computer is repaired, it is no longer present in the listing.

I would like to make a basis that contains the history of these records, a basis which we will call, for example, "historic". Based on N-day, "historic" will contain all N-day records; on day N+1, historical will contain N records and new N+1 records, while the historical N+2 day will contain N records, new N+1 records and new N+2 records, and so on...

The first drawback I encounter: What I managed to do is add to "historic" the new records of the day I process, but I lose the records of the previous days. That is, if I process on the N+2 day, I do not know the records of day N+1. I used to append consultation, but clearly that's not the way, or I'm missing some previous step.

The second drawback: I want to record the repair date of the equipment. Conceptually, if ID_equipo was present on day N and the N+1 day is not present, then it was repaired and report date would be the repair date. I'll face this by combining left external query, between "historical" and the daily basis, and where there's error, it's the ones I need to record. I managed to take those records to a "repaired" table by assigning the report day as a repaired day, but when processing the N+2 day, all repaired on day N+1 would go on to have a repair date as N+2.

Can you help me resolve these issues?

From now on, thank you very much for your time

[fedeleu]

thank you for your help @v-yingjl, but what happens when you want to process the next few days?

this only serves me to unite two days?

[v-yingjl]

Hi @fedeleu ,

Union() function can combine many tables with the same structure, please refer:

https://docs.microsoft.com/en-us/dax/union-function-dax

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[fedeleu]

Hello @v-yingjl , thank you for your response.

The day 1 report has this form

Report_Date	Admission_Date	ID_Product	Description	Area
1/11/2020	31/10/2020	1	d1	23
1/11/2020	31/10/2020	2	d2	42
1/11/2020	1/11/2020	3	d1	12
1/11/2020	1/11/2020	4	d3	23

The day 2 report has this form

Report_Date	Admission_Date	ID_Product	Description	Area
2/11/2020	31/10/2020	1	d1	23
2/11/2020	31/10/2020	2	d2	42
2/11/2020	1/11/2020	4	d3	23
2/11/2020	2/11/2020	9	d2	42
2/11/2020	2/11/2020	6	d1	12

In the report on day 2 is not the record corresponding to ID_Product 3 in the table of day 1 because it was repaired. And the last 2 records on day 2 are new income.

The cumulative table I want to get is as follows

Report_Date	Admission_Date	ID_Product	Description	Area	Repaired_Date
1/11/2020	31/10/2020	1	d1	23
1/11/2020	31/10/2020	2	d2	42
1/11/2020	1/11/2020	3	d1	12	2/11/2020
1/11/2020	1/11/2020	4	d3	23
2/11/2020	2/11/2020	9	d2	42
2/11/2020	2/11/2020	6	d1	12

with this table I can perform several analyses (Power BI outputs)

Segmentation by "Area", by "ID_product", by "Description"

Daily evolution of income/outputs/pending

among other things

I hope this enlargement will serve to find the solution.

Thank you very much again.

[v-yingjl · Accepted solution]

Hi @fedeleu ,

Based on your description, you can create a calculated table like this:

Cumulative Table = 
VAR tab =
    UNION ( 'Day 1', 'Day 2' )
VAR tb =
    ADDCOLUMNS (
        FILTER (
            tab,
            [Report_Date]
                = MINX (
                    FILTER ( tab, [ID_Product] = EARLIER ( 'Day 1'[ID_Product] ) ),
                    [Report_Date]
                )
        ),
        "Repaired Date",
            IF (
                NOT ( 'Day 1'[ID_Product] IN DISTINCT ( 'Day 2'[ID_Product] ) ),
                MAXX ( tab, [Report_Date] ),
                BLANK ()
            )
    )
RETURN
    tb

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yingjl]

Hi @fedeleu ,

Based on your description, nor certain what is your expected output in power bi, maybe you can consider sharing more details about this issue and a sample .pbix file without sesentive information for further discussion.

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.