Return new/removed values within a data set compared to their entry date

[Anonymous]

Hello all,

I would like to create a report but I am struggeling a lot to find the right DAX command for this problem. Lets say I have the follwing Data table:

Data pull date                      Account Name

01.01.20                                       A

01.01.20                                       B

01.01.20                                       C

02.02.20                                       X

02.02.20                                       B

02.02.20                                       C

03.03.20                                       X

03.03.20                                       B

03.03.20                                       C

03.03.20                                       D

.......                                               ....

This data set should represent the account names we have for a given data pull. One data pull is represented by the same Date of data pull. Now, for the report I want to filter by two Data pull dates (so basically to datapulls) and i want to get listed which accounts got removed and or added compared to the older data pull. As an example here I want to filter out 01.01.20 and 03.03.20 and then as a result is should see removals (here A) and added (here X,D). I would be very happy if somebody can help me out here.

Thank you very much

[lbendlin · Accepted solution]

Have been looking at this for a while, here's what I have so far.

Current Day's list

DayList = 
var d = SELECTEDVALUE('Table (2)'[Date])
var v = CALCULATETABLE('Table (2)',allselected('Table (2)'),'Table (2)'[Date]=d)
return CONCATENATEX(v,'Table (2)'[Account],",")

Previous Day's list

Prevlist = 
var d = SELECTEDVALUE('Table (2)'[Date])
var p = CALCULATE(max('Table (2)'[Date]),allselected('Table (2)'),'Table (2)'[Date]<d)
var v = CALCULATETABLE('Table (2)',allselected('Table (2)'),'Table (2)'[Date]=p)
return CONCATENATEX(v,'Table (2)'[Account],",")

Sadly, DAX doesn't have Power Query's List.Difference function so from here you need to do the additions (items that are on the daylist but not the prevlist)  and removals (items that are not on the daylist but are on the prevlist)  yourself. Probably something with the EXCEPT() function.

[lbendlin · Accepted solution]

Have been looking at this for a while, here's what I have so far.

Current Day's list

DayList = 
var d = SELECTEDVALUE('Table (2)'[Date])
var v = CALCULATETABLE('Table (2)',allselected('Table (2)'),'Table (2)'[Date]=d)
return CONCATENATEX(v,'Table (2)'[Account],",")

Previous Day's list

Prevlist = 
var d = SELECTEDVALUE('Table (2)'[Date])
var p = CALCULATE(max('Table (2)'[Date]),allselected('Table (2)'),'Table (2)'[Date]<d)
var v = CALCULATETABLE('Table (2)',allselected('Table (2)'),'Table (2)'[Date]=p)
return CONCATENATEX(v,'Table (2)'[Account],",")

Sadly, DAX doesn't have Power Query's List.Difference function so from here you need to do the additions (items that are on the daylist but not the prevlist)  and removals (items that are not on the daylist but are on the prevlist)  yourself. Probably something with the EXCEPT() function.

[lbendlin · Accepted solution]

Here are the additions:

Additions = 
var d = SELECTEDVALUE('Table (2)'[Date])
var vd = CALCULATETABLE('Table (2)',allselected('Table (2)'),'Table (2)'[Date]=d)
var svd = SELECTCOLUMNS(vd,"Acct",[Account])
var p = CALCULATE(max('Table (2)'[Date]),allselected('Table (2)'),'Table (2)'[Date]<d)
var vp = CALCULATETABLE('Table (2)',allselected('Table (2)'),'Table (2)'[Date]=p)
var svp = SELECTCOLUMNS(vp,"Acct",[Account])
var a = EXCEPT(svd,svp) 
return CONCATENATEX(a,[Acct],",")

And here the removals:

Removals = 
var d = SELECTEDVALUE('Table (2)'[Date])
var vd = CALCULATETABLE('Table (2)',allselected('Table (2)'),'Table (2)'[Date]=d)
var svd = SELECTCOLUMNS(vd,"Acct",[Account])
var p = CALCULATE(max('Table (2)'[Date]),allselected('Table (2)'),'Table (2)'[Date]<d)
var vp = CALCULATETABLE('Table (2)',allselected('Table (2)'),'Table (2)'[Date]=p)
var svp = SELECTCOLUMNS(vp,"Acct",[Account])
var a = EXCEPT(svp,svd) 
return CONCATENATEX(a,[Acct],",")

[Anonymous]

Hi,

thank you very much for your efforts thats super kind of you (Sorry for the delayed answer you were pretty fast). I tried to generate the report with a table visualization but somehow the report only shows me the additions. I think the mistake is on my side. Since I am very new to Power BI can you help me out on where or in which fields I should drag and drop the calculated measures you coded for me. Sorry if this question is to amaeteurish.

[lbendlin]

Yes, the terminology is confusing at first. These need to be created as measures, not as calculated columns.

[Anonymous]

Okay, I got it thank you. I got one last problem now. The output of additions and removals looks now like that:

A

A

A

B

C

D

D

D

....

is there a way that I could sum up the ones with the same name and list them once and the quantity next to the name? In this example it would be like

A (3)

B (1)

C (1)

D (3)

Thank you very much for your efforts.

[lbendlin]

Once per what? You didn't't specify your grouping criteria.

[Anonymous]

Not sure what you mean let me explain it to you...

The output of the removals and or additions compared to the previous Data pull looks like that:

A

A

A

B

B

C

C

D

Here you can see that it is listing me the removals and or added Account names with some duplicates. (The Data pull contains duplicates as well thats why...). My questions is basically if there is a command for only returning single Account names. So no duplicates...

I tried to use:

return CONCATENATEX(VALUE(a,[Acct]," 

")

but the input data for VALUE is already wrong. The result should be like that 

A 

B 

C 

D 

So now I can see that compared to the previous Data pull A got removed and 2 B and so on... 

If I understood you correctly my grouping criteria would be same Accounts (names).

Hopefully thats a little bit clrearer now.

Thank you