Create new table based on row data

[technologyLMM]

Hi all,

i need to create a table from an initial table, but some calculations shall be performed for several rows/columns. 

Please note below the example and advise if this is feasible to be done 🙂

Initial Table:

Date	Start	End	Position	Fuel
18/10/2022	yes	-	1	10
19/10/2022	-	-	2	13
20/10/2022	-	yes	3	11
21/10/2022	yes	-	4	12
22/10/2022	-	-	5	12
23/10/2022	-	-	6	13
24/10/2022	-	yes	7	10

The new table shall be like this

Start Date	End Date	Start Position	End Position	Total Fuel (sum)
18/10/2022	20/10/2022	1	3	34
21/10/2022	24/10/2022	4	7	47

Kindly advise if this can be performed in the powerbi and what formulas shall be used.

Your valuable assistance on the above will be highly appreciated.

[technologyLMM]

Dear @Jihwan_Kim you are absolutely perfect!!

Many many thanks for your valuable contribution and assistance.

It it the first time working with VAR, so your support is much appreciated.

I have two additional columns that i need to implement in the current project and may affect the prepared code.

1) column "Car ID".

2) "extra fuel" column, which shall be also summarized in the final table (Fuel + Extra Fuel).

Initial Table:

Date	Car ID	Start	End	Position	Fuel	Extra Fuel
18/10/2022	A	yes	-	AAA	10	0
19/10/2022	A	-	-	BBB	13	0
20/10/2022	A	-	yes	CCC	11	1
21/10/2022	A	yes	-	DDD	12	0
22/10/2022	A	-	-	EEE	12	2
23/10/2022	A	-	-	ZZZ	13	3
24/10/2022	A	-	yes	AAA	10	1
25/10/2022	A	yes	-	BBB	10	0
26/10/2022	A	-	-	AAA	13	0
27/10/2022	A	-	-	BBB	11	1
28/10/2022	A	-	-	CCC	12	0
29/10/2022	A	-	-	DDD	12	2
30/10/2022	A	-	-	EEE	12	2
31/10/2022	A	-	-	ZZZ	10	1
01/11/2022	A	-	yes	KKK	10	1
18/10/2022	B	yes	-	AAA	30	0
19/10/2022	B	-	-	BBB	42	0
20/10/2022	B	-	-	CCC	20	1
21/10/2022	B	-	-	DDD	30	4
22/10/2022	B	-	-	EEE	43	5
23/10/2022	B	-	-	ZZZ	23	2
24/10/2022	B	-	yes	LLL	32	5
25/10/2022	B	yes	-	AAA	32	0
26/10/2022	B	-	yes	BBB	21	0
21/10/2022	C	yes	-	AAA	20	4
22/10/2022	C	-	-	BBB	21	5
23/10/2022	C	-	yes	CCC	21	2

The new table shall be like this

Start Date	End Date	Car ID	Start Position	End Position	Total Fuel (sum)
18/10/2022	20/10/2022	A	AAA	CCC	35
21/10/2022	24/10/2022	A	DDD	AAA	53
25/10/2022	01/11/2022	A	BBB	KKK	85
18/10/2022	24/10/2022	B	AAA	LLL	236
25/10/2022	26/10/2022	B	AAA	BBB	53
21/10/2022	23/10/2022	C	AAA	CCC	73

Could you please advise how shall your code be amended to depict the final table?

Thank you in advance for your extreme support.

[Jihwan_Kim]

Hi,

Sorry that I cannot understand how CAR column looks like and how it plays in the table.

Please provide the full range of your sample, instead of showing CAR = A only.

[technologyLMM]

I have updated the table in my above message to include additional data, thus becoming more clear.

Thank you in advance for your great suppport

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message.

Please check the attahced file, and the below DAX formula.

New TableV2 = 
VAR _start =
    SELECTCOLUMNS (
        SUMMARIZE (
            FILTER ( DataV2, DataV2[Start] = "yes" ),
            DataV2[Date],
            DataV2[Car ID]
        ),
        "@Start Date", DataV2[Date],
        "@car", DataV2[Car ID]
    )
VAR _startindex =
    ADDCOLUMNS (
        _start,
        "@index",
            COUNTROWS (
                FILTER (
                    _start,
                    [@Start Date] <= EARLIER ( [@Start Date] )
                        && [@car] = EARLIER ( [@car] )
                )
            )
    )
VAR _end =
    SELECTCOLUMNS (
        SUMMARIZE (
            FILTER ( DataV2, DataV2[End] = "yes" ),
            DataV2[Date],
            DataV2[Car ID]
        ),
        "@End Date", DataV2[Date],
        "@car2", DataV2[Car ID]
    )
VAR _endindex =
    ADDCOLUMNS (
        _end,
        "@index2",
            COUNTROWS (
                FILTER (
                    _end,
                    [@End Date] <= EARLIER ( [@End Date] )
                        && [@car2] = EARLIER ( [@car2] )
                )
            )
    )
VAR _position =
    ADDCOLUMNS (
        FILTER (
            GENERATE ( _startindex, _endindex ),
            [@index] = [@index2]
                && [@car] = [@car2]
        ),
        "@startposition",
            MAXX (
                FILTER (
                    DataV2,
                    DataV2[Date] = EARLIER ( [@Start Date] )
                        && DataV2[Car ID] = EARLIER ( [@car] )
                ),
                DataV2[Position]
            ),
        "@endposition",
            MAXX (
                FILTER (
                    DataV2,
                    DataV2[Date] = EARLIER ( [@End Date] )
                        && DataV2[Car ID] = EARLIER ( [@car] )
                ),
                DataV2[Position]
            )
    )
VAR _total =
    ADDCOLUMNS (
        _position,
        "@total",
            SUMX (
                FILTER (
                    DataV2,
                    DataV2[Date] >= EARLIER ( [@Start Date] )
                        && DataV2[Date] <= EARLIER ( [@End Date] )
                        && DataV2[Car ID] = EARLIER ( [@car] )
                ),
                DataV2[Fuel] + DataV2[Extra Fuel]
            )
    )
RETURN
    SUMMARIZE (
        _total,
        [@Start Date],
        [@End Date],
        [@startposition],
        [@endposition],
        [@total]
    )

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

New Table = 
VAR _start =
    SELECTCOLUMNS (
        SUMMARIZE ( FILTER ( Data, Data[Start] = "yes" ), Data[Date] ),
        "@Start Date", Data[Date]
    )
VAR _startindex =
    ADDCOLUMNS (
        _start,
        "@index", COUNTROWS ( FILTER ( _start, [@Start Date] <= EARLIER ( [@Start Date] ) ) )
    )
VAR _end =
    SELECTCOLUMNS (
        SUMMARIZE ( FILTER ( Data, Data[End] = "yes" ), Data[Date] ),
        "@End Date", Data[Date]
    )
VAR _endindex =
    ADDCOLUMNS (
        _end,
        "@index2", COUNTROWS ( FILTER ( _end, [@End Date] <= EARLIER ( [@End Date] ) ) )
    )
VAR _position =
    ADDCOLUMNS (
        FILTER ( GENERATE ( _startindex, _endindex ), [@index] = [@index2] ),
        "@startposition", MAXX ( FILTER ( Data, Data[Date] = EARLIER ( [@Start Date] ) ), Data[Position] ),
        "@endposition", MAXX ( FILTER ( Data, Data[Date] = EARLIER ( [@End Date] ) ), Data[Position] )
    )
VAR _total =
    ADDCOLUMNS (
        _position,
        "@total",
            SUMX (
                FILTER (
                    Data,
                    Data[Position] >= EARLIER ( [@startposition] )
                        && Data[Position] <= EARLIER ( [@endposition] )
                ),
                Data[Fuel]
            )
    )
RETURN
    SUMMARIZE (
        _total,
        [@Start Date],
        [@End Date],
        [@startposition],
        [@endposition],
        [@total]
    )