Help calculate number of products lost purchase from 90 days

[nguyenhieu]

Dear everyone,

I want to calculate number of products lost purchase from 90 days. I have measure as below but the result is blank.

I do not know reason, please help.

The measure: 

Number of days lost purchase products =

IF([LastOrder] >= SELECTEDVALUE('Calendar'[Date]), 0,

    IF(ISBLANK([LastOrder]), BLANK(),    

    DATEDIFF( [LastOrder],

    SELECTEDVALUE('Calendar'[Date]),

    DAY

)))

and

Number of products lost purchase from 90 days =

var _table_ngungmua = ADDCOLUMNS(SUMMARIZE('Sales', Sales[CUSTOMER_ID], Sales[PRODUCT_CODE]),"_Ngungmua",[Number of days lost purchase products])

RETURN

COUNTROWS(

    FILTER(_table_ngungmua, [_Ngungmua] >=90

))

[bhanu_gautam]

@nguyenhieu Try using

Measure to calculate the number of days since the last purchase

DAX
Number of days lost purchase products =
VAR LastOrderDate = [LastOrder]
VAR SelectedDate = SELECTEDVALUE('Calendar'[Date])
RETURN
IF(
LastOrderDate >= SelectedDate,
0,
IF(
ISBLANK(LastOrderDate),
BLANK(),
DATEDIFF(LastOrderDate, SelectedDate, DAY)
)
)

Measure to calculate the number of products with no purchase in the last 90 days

DAX
Number of products lost purchase from 90 days =
VAR _table_ngungmua =
ADDCOLUMNS(
SUMMARIZE('Sales', Sales[CUSTOMER_ID], Sales[PRODUCT_CODE]),
"_Ngungmua", [Number of days lost purchase products]
)
RETURN
COUNTROWS(
FILTER(_table_ngungmua, [_Ngungmua] >= 90)
)

[nguyenhieu]

Thank your for the reply, but it is not working

I provide more information as below

Measure:

LastOrder = CALCULATE(LASTDATE(Sales[ORDER_DATE]),ALL('Calendar'))

[v-saisrao-msft · Accepted solution]

Hi @nguyenhieu,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Thank you for providing detailed context and your current DAX measures. I can see you're trying to calculate the number of products that haven’t been purchased in the last 90 days per customer-product pair, and you're facing an issue where the measure goes blank.

Number of products lost purchase from 90 days :=
VAR SelectedDate = SELECTEDVALUE('Calendar'[Date], TODAY())
VAR ProductsWithLastOrder =
    ADDCOLUMNS(
        SUMMARIZE(Sales, Sales[CUSTOMER_ID], Sales[PRODUCT_CODE]),
        "DaysSinceLastPurchase",
            VAR LastOrderDate =
                CALCULATE(
                    MAX(Sales[ORDER_DATE]),
                    ALLEXCEPT(Sales, Sales[CUSTOMER_ID], Sales[PRODUCT_CODE])
                )
            RETURN
                IF(
                    ISBLANK(LastOrderDate),
                    BLANK(),
                    IF(
                        LastOrderDate >= SelectedDate,
                        0,
                        DATEDIFF(LastOrderDate, SelectedDate, DAY)
                    )
                )
    )
RETURN
COUNTROWS(
    FILTER(
        ProductsWithLastOrder,
        [DaysSinceLastPurchase] >= 90
    )
)

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you. 

[nguyenhieu]

Thank you so much, it worked! ^^