Excel and Power BI Query

[Anonymous]

Site Name	Transaction_End	Sales_Mix_Ind	Payment_Method	Payment_method_Std	Fuel or CR	Fuel	CR	ES	Card
Sedgemoor South	20/05/2018 00:09	F	Euroshell CRT 2	ES
Sedgemoor South	20/05/2018 00:09	C	British Pounds Sterling	cash
Sedgemoor South	20/05/2018 00:09	C	British Pounds Sterling	cash
Sedgemoor South	20/05/2018 00:55	F	Mastercard	card	00:45	00:46
Sedgemoor South	20/05/2018 01:00	F	British Pounds Sterling	cash
Sedgemoor South	20/05/2018 01:46	F	Euroshell	ES	00:45	00:45		01:37
Sedgemoor South	20/05/2018 02:50	F	British Pounds Sterling	cash	01:03	01:03
Sedgemoor South	20/05/2018 03:30	F	VISA	card	00:40	00:40			02:34
Sedgemoor South	20/05/2018 05:09	F	Euroshell CRT	ES	01:39	01:39		03:23
Sedgemoor South	20/05/2018 05:29	C	VISA	card			05:19
Sedgemoor South	20/05/2018 05:29	C	VISA	card					01:59
Sedgemoor South	20/05/2018 05:38	F	British Pounds Sterling	cash
Sedgemoor South	20/05/2018 05:50	C	British Pounds Sterling	cash
Sedgemoor South	20/05/2018 05:50	C	British Pounds Sterling	cash
Sedgemoor South	20/05/2018 05:50	C	British Pounds Sterling	cash
Sedgemoor South	20/05/2018 06:16	C	British Pounds Sterling	cash
Sedgemoor South	20/05/2018 06:18	C	VISA	card					00:49
Sedgemoor South	20/05/2018 06:18	C	VISA	card
Sedgemoor South	20/05/2018 06:18	C	VISA	card
Sedgemoor South	20/05/2018 06:32	F	AllStar	card		00:54
Sedgemoor South	20/05/2018 06:33	C	VISA	card

Need help in automatically calculating the following columns in Excel and Power BI

The columns are "Fuel or CR", "Fuel", "CR", "ES", Card". In this example, I have manually calculated them, but I need the formula for the same.

This is how they need to be calculated

"Fuel or CR": This is straightforward. Difference between two rows of the Transaction_end column

F5 = B5 - B4
F6 = B6 - B5 
and so on and so forth
Note only values greater than 30 mins are mentioned here.

"Fuel",
This depends on the value in the field "Sales_Mix_Ind" depending on whether it is "F"
If its "F", that means Fuel, which means you only substract the corresponding values in the Transaction_end Column
Hence G5 = B5 -B2 as Sales_mix_indicator is "F" in columns C5 and C2
G7 = B6- B5 as Sales_mix_indicator is "F" in columns C6 and C5
Note only values greater than 30 mins are mentioned here.

"CR",

This depends on the value in the field "Sales_Mix_Ind" depending on whether it is "C"
If its "C", that means Fuel, which means you only substract the corresponding values in the Transaction_end Column

Hence H11 = B11 -  B4 as Sales_mix_indicator is "C" in columns C11 and C4
Note only values greater than 30 mins are mentioned here.

"ES",

This depends on the value in the field "Payment_method_std" depending on whether it is "ES"
If its "ES", that means EUroshell, which means you only substract the corresponding values in the Transaction_end Column

Hence I7 = B7 -  B2 as "Payment_method_std"  is "ES" in columns E7 and E2
And I10 = B10 -  B7 as "Payment_method_std"  is "ES" in columns E10 and E7
Note only values greater than 30 mins are mentioned here.

Card".

This depends on the value in the field "Payment_method_std" depending on whether it is "card"
If its "card", that means card payment, which means you only substract the corresponding values in the Transaction_end Column

Hence J9 = B9 -  B5 as "Payment_method_std"  is "card" in columns E9 and E5
And I12 = B12 -  B9 as "Payment_method_std"  is "card" in columns E12 and E9
Note only values greater than 30 mins are mentioned here.

[v-shex-msft]

Hi @Anonymous,

Your table seems contains duplicate records, I'd like to suggest you add a index column to calculate looping table.

Calculated column formulas:

Spoiler

Fuel or CR = 
VAR previous =
    CALCULATE (
        MAX ( Test[Transaction_End] ),
        FILTER ( ALL ( Test ), [Transaction_End] < EARLIER ( Test[Transaction_End] ) )
    )
VAR totalSecond =
    DATEDIFF ( previous, [Transaction_End], SECOND ) + 0
VAR difftime =
    TIME ( INT ( totalSecond / 3600 ), MOD ( totalSecond, 3600 ) / 60, MOD ( MOD ( totalSecond, 3600 ), 60 ) )
RETURN
    IF ( totalSecond >= 1800, difftime, BLANK () )


Fuel = 
VAR previous =
    CALCULATE (
        MAX ( Test[Transaction_End] ),
        FILTER (
            ALL ( Test ),
            [Index] < EARLIER ( Test[Index] )
                && [Sales_Mix_Ind] = "F"
        )
    )
VAR totalSecond =
    DATEDIFF ( previous, [Transaction_End], SECOND ) + 0
VAR difftime =
    TIME ( INT ( totalSecond / 3600 ), MOD ( totalSecond, 3600 ) / 60, MOD ( MOD ( totalSecond, 3600 ), 60 ) )
RETURN
    IF ( [Sales_Mix_Ind] = "F" && totalSecond >= 1800, difftime, BLANK () )


CR = 
VAR previous =
    CALCULATE (
        MAX ( Test[Transaction_End] ),
        FILTER (
            ALL ( Test ),
            [Index] < EARLIER ( Test[Index] )
                && [Sales_Mix_Ind] = "C"
        )
    )
VAR totalSecond =
    DATEDIFF ( previous, [Transaction_End], SECOND ) + 0
VAR difftime =
    TIME ( INT ( totalSecond / 3600 ), MOD ( totalSecond, 3600 ) / 60, MOD ( MOD ( totalSecond, 3600 ), 60 ) )
RETURN
    IF ( [Sales_Mix_Ind] = "C" && totalSecond >= 1800, difftime, BLANK () )


ES = 
VAR previous =
    CALCULATE (
        MAX ( Test[Transaction_End] ),
        FILTER (
            ALL ( Test ),
            [Index] < EARLIER ( Test[Index] )
                && [Payment_method_Std] = "ES"
        )
    )
VAR totalSecond =
    DATEDIFF ( previous, [Transaction_End], SECOND ) + 0
VAR difftime =
    TIME ( INT ( totalSecond / 3600 ), MOD ( totalSecond, 3600 ) / 60, MOD ( MOD ( totalSecond, 3600 ), 60 ) )
RETURN
    IF ( [Payment_method_Std] = "ES" && totalSecond >= 1800, difftime, BLANK () )


Card = 
VAR previous =
    CALCULATE (
        MAX ( Test[Transaction_End] ),
        FILTER (
            ALL ( Test ),
            [Index] < EARLIER ( Test[Index] )
                && [Payment_method_Std] = "Card"
        )
    )
VAR totalSecond =
    DATEDIFF ( previous, [Transaction_End], SECOND ) + 0
VAR difftime =
    TIME ( INT ( totalSecond / 3600 ), MOD ( totalSecond, 3600 ) / 60, MOD ( MOD ( totalSecond, 3600 ), 60 ) )
RETURN
    IF ( [Payment_method_Std] = "Card" && totalSecond >= 1800, difftime, BLANK () )

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin Sheng

     Many Thanks for your reply. Your reply has allowed me to get the desired result on a small subset of the data that I had mentioned. However if I try to apply the same logic on a much bigger data set of 1 month (366856 rows), I am getting an out of memory error. I try to create a new column after applying an index on the data but the column creation "keeps working on it" for a long time and gives me an out of memory error.

Is there a way I can resolve this issue.

I have modified your code slightly. In fact I also had devised another method of achieving the same result, before your reply, but both methods are giving me an out of memory error.

Fuel2 =
VAR previous =
    CALCULATE (
        MAX ( Original[Transaction_End] ),
        FILTER (
            ALL ( Original ),
            [Index] < EARLIER ( Original[Index] )
                && [Sales_Mix_Ind] = "F"
        )
    )
VAR totalminute =
    DATEDIFF ( previous, [Transaction_End], MINUTE ) + 0
RETURN
    IF ( [Sales_Mix_Ind] = "F" && totalminute >= 60, totalminute, BLANK () )

LagColumn F = DATEDIFF(Original[Transaction_end]
                , CALCULATE(MAX(Original[Transaction_end]),
                    FILTER(original,original[Sales_Mix_Ind]="F"),
                    FILTER(Original,
                        Original[Sales_Mix_ind] = EARLIER(Original[Sales_Mix_ind]) &&
                        Original[Transaction_end] < EARLIER(Original[Transaction_end])
                    )
                )
            , MINUTE
            )

Would be great if you can help me resolve this issue

Rgds

Amit

[v-shex-msft]

Hi @Anonymous,

Which version of power bi desktop you used? If you are working on 32 bit version, I'd like to suggest you use 64 bit to increase application memory limit.(32 bit application has 2G memory usage limit)

In addition, your calculation need to looping whole table to compare current and previous records to get diff. It obviously cost lots of memory resource when you use a lot similar calculations formulas.

For this scenario, I'd like to suggest you add more conditions to reduce loop range to optimization memory usage.

Regards,
Xiaoxin Sheng