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