Billing Cycle Calculation (Rank, Count) field

[Anonymous]

We have order transaction data table, in which we want to add a calculated field for Billing cycle. This Billing cycle will be used as a slicer in the model.  We are calculating Billing cycle already with below DAX, but we believe it is not efficient. Also in 2nd scenario, it is returning the wrong Cycle.

Business logic: A billing cycle will complete witht he status of SHIPPED (or anything else than Declined). If my attempts are declined, then the billing cycle would be same till my next attempt for this cycle is <> Declined.  Can we ranked with Order ID as well?

DAX : Any simpler calculation would be appreciated.

Billing cycle =
IF ( 'data'[order_status] = "DECLINED", 0, -1 )
    + CALCULATE (
        COUNT ( 'data'[Unique key] ),
        FILTER (
            FILTER (
                ALL ( 'data' ),
                'data'[Unique key] = EARLIER ( 'data'[Unique key] )
                    && RELATED ( 'product'[is_recurring] ) = "YES"
            ),
            [order_status] <> "DECLINED"
                && 'data'[order_id] <= EARLIEST ( 'data'[order_id] )
        )
    )

Scenario 1 (Existing DAX works fine and according to logic).

bill_email	Unique key	Attempt #	Billing Cycle	Status	OID	Date of Sale
0227@gmail.com	0227massey@gmail.comPROD1	1	0	SHIPPED	10780243	9/7/2018
0227@gmail.com	0227massey@gmail.comPROD1	1	1	SHIPPED	11094903	10/7/2018
0227@gmail.com	0227massey@gmail.comPROD1	1	2	SHIPPED	11469149	11/6/2018
0227@gmail.com	0227massey@gmail.comPROD1	1	3	DECLINED	11887855	12/6/2018
0227@gmail.com	0227massey@gmail.comPROD1	2	3	SHIPPED	11903296	12/7/2018
0227@gmail.com	0227massey@gmail.comPROD1	1	4	DECLINED	12266062	1/6/2019
0227@gmail.com	0227massey@gmail.comPROD1	2	4	SHIPPED	12279257	1/7/2019
0227@gmail.com	0227massey@gmail.comPROD1	1	5	DECLINED	12627618	2/6/2019
0227@gmail.com	0227massey@gmail.comPROD1	2	5	DECLINED	12635619	2/6/2019
0227@gmail.com	0227massey@gmail.comPROD1	3	5	SHIPPED	12640293	2/7/2019
0227@gmail.com	0227massey@gmail.comPROD1	1	6	SHIPPED	13039734	3/9/2019
0227@gmail.com	0227massey@gmail.comPROD1	1	7	SHIPPED	13340104	4/9/2019

Scenario 2: The same dax above is giving a wrong result in this table. Look at line # 8 in below table, expected billing cycle value was 7, where as it give 8 as a result of above dax.

bill_email	Unique key	Attempt #	Billing Cycle	Expected Value	Status	OID	Date of Sale	Line #
23a@gmail.com	23a@gmail.comPROD1	1	0	0	NEW	10810420	9/11/2018	1
23a@gmail.com	23a@gmail.comPROD1	1	1	1	SHIPPED	11156804	10/11/2018	2
23a@gmail.com	23a@gmail.comPROD1	1	2	2	SHIPPED	11541752	11/10/2018	3
23a@gmail.com	23a@gmail.comPROD1	1	3	3	SHIPPED	11939840	12/10/2018	4
23a@gmail.com	23a@gmail.comPROD1	1	4	4	SHIPPED	12300308	1/9/2019	5
23a@gmail.com	23a@gmail.comPROD1	1	5	5	SHIPPED	12739976	2/8/2019	6
23a@gmail.com	23a@gmail.comPROD1	1	6	6	SHIPPED	13053122	3/10/2019	7
23a@gmail.com	23a@gmail.comPROD1	1	8	7	DECLINED	13343265	4/10/2019	8
23a@gmail.com	23a@gmail.comPROD1	1	7	7	SHIPPED	13346120	4/10/2019	9
23a@gmail.com	23a@gmail.comPROD1	2	8	8	DECLINED	13349760	4/11/2019	10
23a@gmail.com	23a@gmail.comPROD1	3	8	8	SHIPPED	13364521	4/13/2019	11

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can create column Rank_order to rank the [order_id].

Rank_order = RANKX(ALL(data),data[order_id],,ASC)

The result using DAX below which have deleted the filter "&& RELATED ('product'[is recurring]) = "YES"" is your expected value, please check if this filter condition is necessary.

Billing cycle =

IF ( 'data'[order_status] = "DECLINED", 0, -1 )

+ CALCULATE (

  COUNT ( 'data'[Unique key] ),

  FILTER (

    FILTER (

    ALL ( 'data' ),

   'data'[Unique key] = EARLIER ( 'data'[Unique key] )

    ),

  [order_status] <> "DECLINED"

  && 'data'[Rank_order] <= EARLIEST ( 'data'[Rank_order] )

  ))

 

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EYevNAHt82tMq20JNqL4unsBLypQtj0LEaZoiAnW7eQjOA?e=pFkEVa

Best Regards,

Amy

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