topic Re: Help calculate number of products lost purchase from 90 days in DAX Commands and Tips

Help calculate number of products lost purchase from 90 days

nguyenhieu — Thu, 24 Apr 2025 08:19:11 GMT

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

))

Re: Help calculate number of products lost purchase from 90 days

bhanu_gautam — Thu, 24 Apr 2025 08:47:06 GMT

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)
)

Re: Help calculate number of products lost purchase from 90 days

nguyenhieu — Thu, 24 Apr 2025 08:53:05 GMT

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'))

Re: Help calculate number of products lost purchase from 90 days

v-saisrao-msft — Wed, 30 Apr 2025 03:51:03 GMT

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.

Re: Help calculate number of products lost purchase from 90 days

nguyenhieu — Wed, 30 Apr 2025 15:14:33 GMT

Thank you so much, it worked! ^^