Using end effective date and start effective date to count changes to individuals' scores

[sschumann]

Its easiest if I start with an example of the data for one individual:

startdate	enddate	score	individual id
4/25/2023	4/25/2023	4 – Hot Lead	123
4/25/2023		4 – Hot Lead	123
1/18/2023	1/18/2023	2 – Cool Lead	123
1/18/2023	1/18/2023	2 – Cool Lead	123
1/18/2023	4/25/2023	3 – Warm Lead	123
1/18/2023	4/25/2023	3 – Warm Lead	123

I need to calculate the number of times individuals move from Cool->Warm, Cool->Hot, Warm->Hot, Hot->Warm, Hot->Cool, Warm->Cool. So for the individual 123, I want it to return 1 Cool->Warm and 1 Warm->Hot (happened on 1/18 and 4/25 for reference). The goal is aggregate all individuals to see how many times did sales people move them from A->B in a given time period. Is there anyway to do this within DAX? Can I get a pointer to any dax command that might be useful here?

Its all in an offprem powerbi dataset so I can use power query to transform if necessary...(Trying very hard not to rant about DAX)

[v-yueyunzh-msft · Accepted solution]

Hi , @sschumann 

According to your description, you want to "Using end effective date and start effective date to count changes to individuals' scores".

Here are the steps you can refer to :
(1)This is my test data:

(2)We need to convert the table in Power Query Editor , you can put this M code in "Advanced Editor to refer to":

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE3MtU3MjAyVtJBZSs8apis4JFfouCTmpgCFDAECsfqoGrAp85Q39ACpg6ZbQTW4Jyfn0NdHcjuMgbrCE8syqWuDhTd6B43MjbBCCBkNqY3wDpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [startdate = _t, enddate = _t, score = _t, #"individual id" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"startdate", type date}, {"enddate", type date}, {"score", type text}, {"individual id", Int64.Type}}),
    #"Removed Duplicates" = Table.TransformColumns(Table.Distinct(#"Changed Type"),{"enddate",(x)=> if x=null then Date.From(DateTime.FixedLocalNow()) else x   }),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Date", each if [startdate]=[enddate] then {[startdate]} else List.Dates(Date.AddDays( [startdate],1),Duration.Days(Duration.From([enddate]-Date.AddDays( [startdate],1)))  , #duration(1,0,0,0)    )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"startdate", "enddate"}),
    #"Expanded Date" = Table.ExpandListColumn(#"Removed Columns", "Date"),
    #"Removed Duplicates1" = Table.Distinct(#"Expanded Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Duplicates1",{{"Date", type date}}),
    Custom1 = Table.Group(#"Changed Type1","individual id",{"test",(x)=>Table.AddIndexColumn(Table.Sort(x,"Date"), "Index", 1, 1, Int64.Type)}),
    #"Expanded test" = Table.ExpandTableColumn(Custom1, "test", {"score", "Date", "Index"}, {"score", "Date", "Index"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded test",{{"Date", type date}, {"Index", Int64.Type}, {"score", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Custom", each Text.Start( [score] ,1)),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Custom", "Score_Index"}})
in
    #"Renamed Columns"

(3)Then we need to create a table as dimension and we do not need to create any relationship between tables:

(4)Then we can create a measure like this:

Measure = var _cur_row = MAX('Table 2'[Index])
var _t2 = ADDCOLUMNS('Table' , "flag" , var _test =MAXX( FILTER('Table' , [Index]<EARLIER('Table'[Index])) ,[Score_Index]) return IF([Index]=1,0, IF([Score_Index] <> _test ,1,0)))
var _t3 = ADDCOLUMNS( _t2 , "Flag2" , var _falg = [flag]  var _test=MAXX( FILTER('Table' , [Index]<EARLIER('Table'[Index])) ,[Score_Index])  var _score = [Score_Index] return 
IF( _falg =1 , SWITCH(TRUE() , _test="2"&&_score="3",1,_test="2"&&_score="4",2,_test="4"&&_score="2",3,_test="4"&&_score="3",4,_test="3"&&_score="2",5,_test="3"&&_score="4",6)     , BLANK()))
return
COUNTROWS(FILTER(_t3 ,[Flag2] = _cur_row))

Then we can put the fields we need on the visual and we can get this:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly