How to compare Current Snapshot data with Previous Snapshot and Find new Sales Orders in Power BI using DAX

Power BI DAX to compare Current Snapshot data with Previous Snapshot and Find new Sales Orders
Scenario:

Lets suppose, we sample snapshots of Current Month and Previous Month sales orders data as follows.

By comparing the Current Snapshot data with Previous Snapshot, we need identify the Count of new Orders received from each Customer.

The Customer table is a Related Table of Current Snapshot but not to the Previous Snapshot.

Current Snapshot (say as of 1st Nov2021) : Sales_CurSnapshot

Previous Snapshot (say as of 1st Oct2021) : Sales_PrevSnapshot

The Model of the Power BI looks as follows :

# ---------------------------------------------------------------------------------------- #

Now we can find the Count of new Orders received in Current Snapshot data from each of the Customer, by comparing the Current Snapshot data with Previous Snapshot, using the below DAX Expression:

NewOrdersSumamry =

/* VAR vTotalSales = Calculate(SUM(Sales_PrevSnapshot[Gross_Sales])) */

VAR vSalesSummary = ADDCOLUMNS(

FILTER(

SELECTCOLUMNS(Sales_CurSnapshot,"OrderId", Sales_CurSnapshot[Order_Id],

"Custmr_Name", RELATED(tbl_Customers[Cust_Name]),

"PrevSales", Calculate(SUM(Sales_PrevSnapshot[Gross_Sales]))),

[OrderId]<>BLANK() && ISBLANK([PrevSales]) ),

"OrderStatus","New")

VAR vNewOrdersPerCustomer = SUMMARIZE(vSalesSummary, [Custmr_Name],

"Count_NewOrders", COUNT(Sales_CurSnapshot[Order_Id]))

VAR vNewOrders = SELECTCOLUMNS(vSalesSummary,"NewOrders", [OrderId])

RETURN

vNewOrdersPerCustomer 

Result :

# ---------------------------------------------------------------------------------------- #

From the same above expression the vSalesSummary will return the Orders per Customer with no Sales in the Previous Snapshot.

Notes:

Please note that, you can achieve the above Scenario, using various simple techniques like with use of LOOKUPVALUE, based on Order_Id, we can verify whether it is a new Order only appearing in the Current Snapshot and not in the Previous Snapshot.

However, in this article, i just tried to explain, how we can get the result using the combination of various Functions, which helps in improving Query building capabilities.

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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional 

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