Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[samaran ks]

How can sum of sales amount with Slowly Changing dimension by employee (Sales person) from the below data model.

Below is the requirement and table structure.

My Data Model

 

Fact Table:

SalesOrderHeaser : Contains the information about OrderId, Order Date , ship method and Territory History ID

ShipMethod = 1 – Sales by WebSite

Shipmetnod = 5 – Sales by Employee (Sales Person)

Key point is to create measure for sum of totals if the Shipmethod = 5 then employee and shipmethd=1 then Website.

SalesOrderID is the relation ship key of lookup table SalesOrderDetail

SalesTerritoryHistory is Slowly changing dimension by Employee and Territory. Employee will be mapped to single territory by start date and end date over the period.

Employee table contains sales person id and connected to Sales Territory History(SCD table)

Employee Business Entity Id is primary key of Employee table

SalesOrderDetails : Contains Totals Sales Column and relationship with SalesOrderHeader table by SalesOrderId

Table structure

SalesOrderHeader

 

SalesOrderDetails

 

Employee

 

SalesTerritoryHistory

 

SalesTerritory

 

 

Final Output

 

 

Anybody can help on this slowly changing dimension

——————————
samaran ks
UAE
——————————