Count new sale order ID and existing sale order from past 1 and 2 month

[NH]

Hi

Hope someone can help to advise me on who to write the Dax formulae to check whether the  monthly Sale order is a new order or repeated order from previous month  (mth-1) and   also from the month before (mth-2) where mth = current month.

Example of my sale order table as below.

Sale Date	Sale Order ID	Qty
02-Jan-18	1	1
04-Jan-18	2	2
06-Jan-18	3	5
25-Jan-18	9	10
03-Feb-18	4	2
08-Feb-18	1	4
10-Feb-18	2	6
28-Feb-18	5	8
04-Mar-18	1	2
10-Mar-13	6	2
10-Mar-18	2	2
25-Mar-18	4	2
29-Mar-18	8	2

Result:

Mth_Yr	New Sale Order ID count	Previous Month Sale Order ID Count	Sale Order ID count happent to in Mth -1 & Mth-2
Feb-18	2	2
Mar-18	2	3	1

Thanks

[v-shex-msft · Accepted solution]

Hi @NH,

I think you can try to use EXCEPT and INTERSECT functions to get the specific order count.

Measure formula:

Spoiler

New ID =
VAR current_Date =
    MAX ( 'Sales month'[Sale Date] )
VAR current_id =
    CALCULATETABLE (
        VALUES ( 'Sales month'[Sale Order ID] ),
        FILTER (
            ALLSELECTED ( 'Sales month' ),
            FORMAT ( [Sale Date], "mm/yyyy" ) = FORMAT ( current_Date, "mm/yyyy" )
        )
    )
VAR previous_id =
    CALCULATETABLE (
        VALUES ( 'Sales month'[Sale Order ID] ),
        FILTER (
            ALLSELECTED ( 'Sales month' ),
            [Sale Date] < DATE ( YEAR ( current_Date ), MONTH ( current_Date ), 1 )
        )
    )
RETURN
    COUNTROWS ( EXCEPT ( current_id, previous_id ) )


Previous ID =
VAR current_Date =
    MAX ( 'Sales month'[Sale Date] )
VAR current_id =
    CALCULATETABLE (
        VALUES ( 'Sales month'[Sale Order ID] ),
        FILTER (
            ALLSELECTED ( 'Sales month' ),
            FORMAT ( [Sale Date], "mm/yyyy" ) = FORMAT ( current_Date, "mm/yyyy" )
        )
    )
VAR previous_id =
    CALCULATETABLE (
        VALUES ( 'Sales month'[Sale Order ID] ),
        FILTER (
            ALLSELECTED ( 'Sales month' ),
            FORMAT ( [Sale Date], "mm/yyyy" )
                = FORMAT (
                    DATE ( YEAR ( current_Date ), MONTH ( current_Date ) - 1, 1 ),
                    "mm/yyyy"
                )
        )
    )
RETURN
    COUNTROWS ( EXCEPT ( previous_id, current_id ) )


Previous 2 ID =
VAR current_Date =
    MAX ( 'Sales month'[Sale Date] )
VAR current_id =
    CALCULATETABLE (
        VALUES ( 'Sales month'[Sale Order ID] ),
        FILTER (
            ALLSELECTED ( 'Sales month' ),
            FORMAT ( [Sale Date], "mm/yyyy" ) = FORMAT ( current_Date, "mm/yyyy" )
        )
    )
VAR previous_id =
    CALCULATETABLE (
        VALUES ( 'Sales month'[Sale Order ID] ),
        FILTER (
            ALLSELECTED ( 'Sales month' ),
            FORMAT ( [Sale Date], "mm/yyyy" )
                = FORMAT (
                    DATE ( YEAR ( current_Date ), MONTH ( current_Date ) - 1, 1 ),
                    "mm/yyyy"
                )
        )
    )
VAR previous_2_id =
    CALCULATETABLE (
        VALUES ( 'Sales month'[Sale Order ID] ),
        FILTER (
            ALLSELECTED ( 'Sales month' ),
            FORMAT ( [Sale Date], "mm/yyyy" )
                = FORMAT (
                    DATE ( YEAR ( current_Date ), MONTH ( current_Date ) - 2, 1 ),
                    "mm/yyyy"
                )
        )
    )
RETURN
    COUNTROWS (
        INTERSECT (
            INTERSECT ( previous_id, current_id ),
            INTERSECT ( current_id, previous_2_id )
        )
    )

Result:

Regards,

Xiaoxin Sheng

[NH]

Hi XiaoXin,

I need to get the distinct count of the monthly sale order ID for current mth, mth-1 and mth-2 . How can I do it in DAX?

Thanks.

NH

[NH]

Hi XiaoXin,

Thanks for your help. I will try it out and update you the result.

:).

NH