How to Identify which Products are having Sales in selected Date Range using DAX

How to Identity Max Sales Date and Order value per Product using Power BI DAX
Scenario:

Suppose we have a Data Model as follows:

The Relationships are as follows:

Based on the above Data Model, we want to find the which Products are having the sales in the selected Date Range.
If the Product is not having any Sales in that selected period, then we should show its status as In-Active.

Also, we want to find what is the Last Order date and Last Order value per Product in selected Date Range, and in the overall Date Range of Orders.

We can achieve this Scenario using the below DAX expressions:

Units Sold in Selected Date Range = SUM(tbl_Sales[Units_Sold])

Last Order Date per Prod in Selected Date Range = MAX(tbl_OrderDetails[Order_Date])

Last Order Value per Prod in Selected Date Range =

VAR _SelectProd = SELECTEDVALUE(tbl_Products[Prod_Id])

VAR _LastOrder =

    MAXX( FILTER(ALLSELECTED(tbl_OrderDetails), tbl_OrderDetails[Prod_Id]=_SelectProd),

        tbl_OrderDetails[Order_Date]

    )

VAR _LastOrder_Value =

    CALCULATE( SUM(tbl_Sales[Units_Sold]),

        FILTER(ALL(tbl_OrderDetails),tbl_OrderDetails[Order_Date]=_LastOrder))

RETURN

_LastOrder_Value

Is Product InActive in Selected DateRange =

VAR _AllProd =  FILTER(VALUES(tbl_Products[Prod_Id]),

                                                tbl_Products[Prod_Id]<>BLANK()

                                            )

VAR _SoldProd = FILTER(VALUES(tbl_OrderDetails[Prod_Id]) ,

                                                tbl_OrderDetails[Prod_Id]<>BLANK()

                                            )

VAR _ExceptVal = COUNTROWS(EXCEPT(_AllProd, _SoldProd))

RETURN

IF ( AND(ISBLANK(_ExceptVal), [Units Sold in Selected Date Range]),0,_ExceptVal)

Product Sale Status =

SWITCH( TRUE(),

    [Is Product InActive in Selected DateRange] = 1,"In Active",

     [Units Sold in Selected Date Range]<> BLANK(), "Active"

    )

The following measures returns the Last Order_Id, Order_Date and Order Value across Total Orders and all the Order dates per Product.

Last Order Date per Product =

VAR _SelectProd = SELECTEDVALUE(tbl_Products[Prod_Id])

VAR _LastOrder =

    MAXX( FILTER(ALL(tbl_OrderDetails), tbl_OrderDetails[Prod_Id]=_SelectProd),

        tbl_OrderDetails[Order_Date]

    )

RETURN

_LastOrder

Last Order_Id per Product =

VAR _SelectProd = SELECTEDVALUE(tbl_Products[Prod_Id])

VAR _LastOrder =

    MAXX( FILTER(ALL(tbl_OrderDetails), tbl_OrderDetails[Prod_Id]=_SelectProd),

        tbl_OrderDetails[Order_Date]

    )

VAR _LastOrder_Value =

    CALCULATE( LASTNONBLANK(tbl_OrderDetails[Order_Id],TRUE()),

        FILTER(ALL(tbl_OrderDetails),tbl_OrderDetails[Order_Date]=_LastOrder))

RETURN

_LastOrder_Value

Last Order Value per Product =

VAR _SelectProd = SELECTEDVALUE(tbl_Products[Prod_Id])

VAR _LastOrder =

    MAXX( FILTER(ALL(tbl_OrderDetails), tbl_OrderDetails[Prod_Id]=_SelectProd),

        tbl_OrderDetails[Order_Date]

    )

VAR _LastOrder_Value =

    CALCULATE( SUM(tbl_Sales[Units_Sold]),

        FILTER(ALL(tbl_OrderDetails),tbl_OrderDetails[Order_Date]=_LastOrder))

RETURN

_LastOrder_Value

Results:

Notes: In the Calendar table, we can use the following Column expression to set the Flag as 1 if the Calendar Date is falls within the Range of Min and Max of Sale date.

IsSaleDate =

VAR _MaxSaleDt = MAX(tbl_OrderDetails[Order_Date])

VAR _MinSaleDt = (EOMONTH(MIN(tbl_OrderDetails[Order_Date]),-1) +1 )

VAR _SaleDtFlag = INT( DimCalendar[DimDate]>=_MinSaleDt &&                                         DimCalendar[DimDate] <= _MaxSaleDt)

RETURN

_SaleDtFlag

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------