PP Dax measure to calculate sales by rep between 2 dates

[KimC2504]

Hi There,

I need to be able to calculate the sales by rep by account. Ideally they would work on the same account all year to make my life easy but this is not the case. Sometimes they only work on one account for 1 mth, 2mths etc. Could I please get some help for the measure to do this? I have tried to used “Dates between” but I can’t get it to work.

If I use AU123 as an example, the result I want is:

Account Sales Rep Total
AU123 Gabby 350
AU123 Philip 1000
AU123 Rita 1650

Tables are below:

Rep start and end date table
Sales Rep ID Sales Rep Account Start Date End Date
00136341 Gabby AU123 1/11/2014 31/11/2014
00136341 Gabby AU456 1/11/2014 31/10/2015
00136341 Gabby AU111 1/11/2014 31/10/2015
00492889 Philip AU123 1/12/2014 31/12/2014
00492889 Philip AB115 1/11/2014 31/12/2015
00492889 Philip AB116 1/11/2014 31/10/2015
00492889 Philip AB117 1/11/2014 31/10/2015
00492889 Philip AB118 1/11/2014 31/10/2015
00492889 Philip AB119 1/11/2014 31/10/2015
00492889 Philip AB120 1/11/2014 31/10/2015
00492889 Philip AB121 1/11/2014 31/10/2015
04718794 Rita AU123 1/01/2015 31/10/2015
04718794 Rita AB115 1/01/2015 31/10/2015
04718794 Rita RS158 1/11/2014 31/10/2015

Account table
Account NAME
AU123 School
AU456 HOLDINGS
AU111 WESTERN
AB115 UNI
AB116 REACH
AB117 TOPP
AB118 FXC
AB119 MANIC
AB120 TANSIS
AB121 MELBOURNE
RS158 SAFETY

Rep acct table
Sales Rep ID Sales Rep
00136341 Gabby
00492889 Philip
04718794 Rita

Date Table
Date Mth
1/11/2014 Nov
1/12/2014 Dec
1/01/2015 Jan


Sales by account table
Date Account Amount
1/11/2014 AU123 100
1/11/2014 AU456 150
1/11/2014 AU111 200
1/11/2014 AU123 250
1/11/2014 AB115 300
1/11/2014 AB116 350
1/11/2014 AB117 400
1/11/2014 AB118 450
1/11/2014 AB119 500
1/11/2014 AB120 550
1/11/2014 AB121 600
1/12/2014 AU123 650
1/12/2014 AB115 700
1/12/2014 RS158 750
1/12/2014 AU123 100
1/12/2014 AU456 150
1/12/2014 AU111 200
1/12/2014 AU123 250
1/12/2014 AB115 300
1/12/2014 AB116 350
1/12/2014 AB117 400
1/12/2014 AB118 450
1/12/2014 AB119 500
1/12/2014 AB120 550
1/12/2014 AB121 600
1/01/2015 AU123 650
1/01/2015 AB115 700
1/01/2015 RS158 750
1/01/2015 AU123 100
1/01/2015 AU456 150
1/01/2015 AU111 200
1/01/2015 AU123 250
1/01/2015 AB115 300
1/01/2015 AB116 350
1/01/2015 AB117 400
1/01/2015 AB118 450
1/01/2015 AB119 500
1/01/2015 AB120 550
1/01/2015 AB121 600
1/01/2015 AU123 650
1/01/2015 AB115 700
1/01/2015 RS158 750

 

[ImkeF]

Hi Kim,
there are DAX-solutions for it (considering many-to-many aspects and slowly changing dimensions). However, they have downsides (i.e. performance and report design/layout-restrictions), I try to avoid them. Instead I’d do some ETL with Power Query in order to bring the SalesRep into the sales table, then continue with straightforward design:

let
       // 1. Create a Cartesian Product giving all possible combinations of dates and products and create a key on that
    GetDateTbl= Excel.CurrentWorkbook(){[Name="SelectDate"]}[Content],
    GetRepDateTbl = Excel.CurrentWorkbook(){[Name="RepDate"]}[Content],
    PrepareRepDateTbl = Table.AddColumn(GetRepDateTbl, "Binder", each 1),
    PrepDatesTbl = Table.AddColumn(GetDateTbl, "Binder", each 1),    
    JoinRepDateTbl1 = Table.NestedJoin(PrepDatesTbl,{"Binder"},PrepareRepDateTbl,{"Binder"},"NewColumn"),
    CreateCartProduct = Table.ExpandTableColumn(JoinRepDateTbl1, "NewColumn", {"Account", "NAME", "Binder"}, {"NewColumn.Account", "NewColumn.NAME", "NewColumn.Binder"}),
    CreateNewCode = Table.AddColumn(CreateCartProduct, "NewKey", each [NewColumn.Account]&Text.From([Date])),
    RemoveDuplicates = Table.Distinct(CreateNewCode, {"NewKey"}),

      // 2. Join it with RepStartAndEndTbl, filling the StartDate of each SalesRep with its name, sort
    JoinRepDateTbl2 = Table.NestedJoin(RemoveDuplicates,{"Date", "NewColumn.Account"},PrepareRepDateTbl,{"StartDate", "Account"},"NewColumn"),
    ShowSalesRepStart = Table.ExpandTableColumn(JoinRepDateTbl2, "NewColumn", {"SalesRep"}, {"SalesRep"}),
    Sort = Table.Sort(ShowSalesRepStart,{{"NewColumn.Account", Order.Ascending}, {"Date", Order.Ascending}}),
    
      // 3. Fill out all following dates until the next SalesRep takes over with the current SalesRep
    FillOut = Table.FillDown(Sort,{"SalesRep"}),
    ShortenCode = Table.ReplaceValue(FillOut," 00:00:00","",Replacer.ReplaceText,{"NewKey"}),

      // 4. Fetch and prepare SalesTbl
    GetSalesTbl = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    ShortenDate = Table.TransformColumnTypes(GetSalesTbl,{{"Date", type date}}),
    AddNewKey = Table.AddColumn(ShortenDate, "NewKey", each [Account]&Text.From([Date])),
    
      // 5. Join 3. & 4.: Adding the SalesRep to every SalesTransaction
    JoinSales= Table.NestedJoin(AddNewKey,{"NewKey"},ShortenCode,{"NewKey"},"NewColumn", JoinKind.Inner),
    ShowSalesRep_ = Table.ExpandTableColumn(JoinSales, "NewColumn", {"SalesRep"}, {"SalesRep"}),
    CleanUp = Table.RemoveColumns(ShowSalesRep_,{"NewKey"})

in
    CleanUp

 

[KimC2504]

Hi ImkeF,

Wow, that is a lot to understand. Thank you for the response. I will try and get my head around it later today.

Thanks again
Kim

 

[scottsen]

That is some pretty insane M action

I think the easy road here is to add a calculated column for the Acct Rep on the "Sales by account table" table?