Calculated field based on customer history : PowerBI

[mohamedelham]

Hello,

I am a PowerBI new user. I am trying to see how I can create new calculated fields within PowerBI to be able afterwards to use predictive models.

Indicative example:

* To produce predictive variables, I need to enrich each row of the db ("shopping actions" table) with a new field. Each row represents a shopping action from a customer abc (customer id, amount paid, venue date,...). For row "x", I need to have a field (column to add) describing "past buying behaviour from this customer" corresponding at the "x" date in this row (venue date).

I need to go through the whole "shopping actions" table focusing only on rows where customer id = "abc" AND venue date < venue date of row "x".

How can PowerBI DAX functions help me in this please? This calculation should be done for every rows.

Thank you for your help.

Mohamed.

[mohamedelham]

Hello,

I am a PowerBI new user. I am trying to see how I can create new calculated fields within PowerBI to be able afterwards to use predictive models.

Indicative example:

* To produce predictive variables, I need to enrich each row of the db ("shopping actions" table) with a new field. Each row represents a shopping action from a customer abc (customer id, amount paid, venue date,...). For row "x", I need to have a field (column to add) describing "past buying behaviour from this customer" corresponding at the "x" date in this row (venue date).

I need to go through the whole "shopping actions" table focusing only on rows where customer id = "abc" AND venue date < venue date of row "x".

How can PowerBI DAX functions help me in this please? This calculation should be done for every rows.

Thank you for your help.

Kind Regards,

Mohamed.

[v-shex-msft]

Hi @mohamedelham,

Please share some sample data with table format with expected result, it will be help for testing and coding formula.

BTW, dax formula not contain any cache data features, if you means to use dax to record history operations, it is impossible.

Regards,

Xiaxoin Sheng

[mohamedelham]

Hello,

Thank you for your reply. Below and example:

* the fields 1 to 5 are given from client

* the fields from 6 and 7 should be calculated and based on cutomers history

** example: customer 321 came 3 times

*****the 1st stime he was new and then no field to calculate as no history

*****the 2nd time

************value "28" is calculated from difference between current venue date and last venue date //

************value "Paid" is calculated from payment statuts in his last shoppingAction column.

Thx for hep 🙂

ShoppingAction id	customerid	Category	Venue date	Payment status	#days since last Venue	Last Payment status
35158497	855	food	29/09/2016	Paid	17	Paid
54659217	321	food	04/01/2017	Unpaid	unknown	unknow
21548956	321	non food	01/02/2017	Unpaid	28	Unpaid
32658754	215	food	02/03/2017	Paid	1	Paid
54659887	321	non food	01/05/2017	Paid	89	Unpaid
15847989	145	non food	27/09/2017	Paid	2	Paid

[v-shex-msft · Accepted solution]

Hi @mohamedelham,

I think '#days since last Venue','Last Payment status' fields are you wanted, right? If this is a case, please try to use following measures:

History days = 
VAR last_date =
    CALCULATE (
        MAX ( 'Sample'[Venue date] ),
        FILTER ( ALLSELECTED ( 'Sample' ), [Venue date] < MAX ( [Venue date] ) ),
        VALUES ( 'Sample'[customerid] )
    )
RETURN
    IF (
        last_date = BLANK (),
        0,
        COUNTROWS ( CALENDAR ( last_date, MAX ( [Venue date] ) ) ) - 1
    )

Lsat Status = 
VAR last_date =
    CALCULATE (
        MAX ( 'Sample'[Venue date] ),
        FILTER ( ALLSELECTED ( 'Sample' ), [Venue date] < MAX ( [Venue date] ) ),
        VALUES ( 'Sample'[customerid] )
    )
RETURN
    IF (
        last_date <> BLANK (),
        LOOKUPVALUE (
            'Sample'[Payment status],
            Sample[customerid], SELECTEDVALUE ( 'Sample'[customerid] ),
            Sample[Venue date], last_date
        ),
        SELECTEDVALUE ( 'Sample'[Payment status] )
    )

Regards,

Xiaoxin Sheng

[mohamedelham]

Dear  Xiaoxin,

It works perfect. Thank you for your help. The formula are implemented as "measures" and then I use them as calculated columns. 

Kind Regards,

Mohamed.

[mohamedelham]

Hello Xiaoxin Sheng,

Sorry for late answer, I just came back to work and to PowerBI tests. Thank you for your feedback. I will test this asap and share feedback with you. Could you please explain a bit the formula to be sure I understand.

Kind Regards,

Mohamed.

[mohamedelham]

 

 Hello Xiaoxin,

Here is the result I get. The columns 6 and 8 should have the same values as I used the formula on the last column. 

I tried to change a few things but nothing gives the right result (column 6).

Kind Regards,

Mohamed.

[mohamedelham]

Hello,

The formula are actually measures if I understand correctly. It works fine.

Thank you for your help.

Kind regards,

Mohamed.