Forum Discussion

fedeleu's avatar
fedeleu
Frequent Visitor
5 years ago
Solved

Form Table with Historical Data

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 ...
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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.

fedeleu's avatar
fedeleu
Frequent Visitor
5 years ago

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's avatar
v-yingjl
Community Support
5 years ago

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.