Saturday, April 9, 2022

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

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog