Create table filtered with several considerations

[Laocsulak]

Hello,

I need help creating a table where I have transactions paid in installments where the column status has the next characteristics:

Status = COMPLETED -> installment paid
Status = DUE -> installment overdue
Status = PENDING -> installment not paid, but not overdue

I need to create a table of users with the following characteristics:
- Users that have the last installment completed in the current or previous month (in this example in Feb-23 or Jan-23)
- Users that don't have due installments
- Users that don't have a new purchase in the current or previous month, that's to say, they don't have a first installment in the current or previous month

I provide the following dummy example to solve:

The column installment shows if it is the first installment (1), the second installment (2) or the third installment (3). There are purchases made in 2 and 3 installments.

In this example, the table only should contain users 1 and 2:
- User 1 was considered because he has the penultimate installment paid in January, doesn't have due installments, and doesn't have new purchases in January or February
- User 2 was considered because he has the last installment paid in January, doesn't have due installments, and doesn't have new purchases in January or February
- User 3 was not considered because he had an overdue installment
- User 4 was not considered because he had a new purchase in January

Files:

https://drive.google.com/drive/folders/10HO6y8kAdtun4gOUWEOPNOthsc-ktOaW?usp=share_link

Thanks in advance!

[tamerj1]

Hi @Laocsulak 
Please check if this is what you;re looking for.

FilterMeasure = 
VAR CurrentTable = 
    transactions
VAR Condition1 = 
    NOT ISEMPTY ( 
        FILTER ( CurrentTable, transactions[status] = "COMPLETED" )
    )
VAR Condition2 = 
    ISEMPTY ( 
        FILTER ( CurrentTable, transactions[status] = "DUE" )
    )
VAR NumOfOrders = 
    COUNTROWS ( 
        DISTINCT (
            SELECTCOLUMNS ( CurrentTable, "@Order", transactions[order_id] )
        )
    )
VAR Condition3 = 
    NumOfOrders < 2
RETURN
    INT ( Condition1 && Condition2 && Condition3 )

[Anonymous · Accepted solution]

Hi @Laocsulak ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Create 2 measures.

_DUE = IF(MAX('Table 1'[status])="DUE",0,1)

Measure = var _thismonth=TODAY()
var _lastmonth=EDATE(TODAY(),-1)
var _minbuy=CALCULATE(MIN('Table 1'[created_date]),FILTER(ALL('Table 1'),'Table 1'[user]=SELECTEDVALUE('Table 1'[user])&&'Table 1'[installment]=MINX(ALLSELECTED('Table 1'),'Table 1'[installment])))

VAR _DUE=MINX(FILTER(ALLSELECTED('Table 1'),'Table 1'[user]=SELECTEDVALUE('Table 1'[user])),[_DUE])
VAR _NEWBUY=IF(_minbuy>=_lastmonth&&_minbuy<=_thismonth,BLANK(),1)
RETURN 
IF(_DUE=0,BLANK(),IF(_NEWBUY=0,BLANK(),1))

Then you can create a new table.

Table = FILTER(SUMMARIZE('Table 1','Table 1'[user],'Table 1'[order_id],'Table 1'[installment],"aa",[Measure]),[Measure]<>BLANK())

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Laocsulak]

Every user could have many order_id. In this example, users 4, 2, and 1 have only one order_id, and user 3 has two order_id.

[tamerj1]

Hi @Laocsulak 
User 4 has only one order id?! Please clarify this point further.