Previous Value identification

[Vishy]

Hi Team,

I am stuck in a scenario, wherein i need to retrieve the data for a particular user from his past values.

as shown is the table image attached for the current month , i am able to identify that the user was abuser for a particular condition by using count and allexpect dax.

But now the business also wants to know which were those months when he was abuser. So is there any any possibility of creating a dax which concatenates the month name when the same user was having the same abuser description in previous months.

Example in the table - current month attached: -
I have selected 2020 - it tells me that , phone no. 13,16 , 21 had also been abuser previously as well based on the abuser count. Now business needs to know the months when they were abuser previous as well.

I am attaching the sample pbix file and output required.

Test edna.pbix (46.0 KB)
![test|690x107]

[Melissa]

Hi @Vishy,

You can do this by using the CONCATENATEX function. First I added a calculated column to your table

Format Date = FORMAT( Sheet1[Date], "mmmm - yyyy" )
.
Then created the following measure:

Abuse timeline = 
VAR myID = SELECTEDVALUE(Sheet1[id])
VAR myDescr = SELECTEDVALUE(Sheet1[Abuser Descr])
VAR myTable = 
FILTER( ALL( Sheet1 ),
    Sheet1[id] = myID &&
    Sheet1[Abuser Descr] = myDescr
)

RETURN

IF( HASONEVALUE( Sheet1[id] ),
CALCULATE(
    CONCATENATEX( 
            SELECTCOLUMNS( myTable,
            "Format Date",
            [Format Date]
        ),
        [Format Date],
        ", "
    )
),
BLANK()
)

.
This is the result:

I hope this is helpful,

Here’s the file: eDNA Forum - Previous Value identification.pbix (52.1 KB)

[ankit]

Hi @Vishy

I have made 1-2 changes to the Melissa solution. PFD below

1. Date Table was missing. I have added a date table as it’s the best practice and useful in many ways. You can remove it if not desired.
   http://portal.enterprisedna.co/courses/mastering-dax-calculations/lectures/13348495

2. For CurrentMonth, individual Date Selection is done. Have added one column to identify the current month. We can use this in Page filter instead of selecting individual dates.
   IsCurrentMonth =
   IF (
   Dates[MonthInCalendar] = FORMAT ( TODAY (), “MMM YYYY” ),
   “CurrentMonth”,
   “OldMonths”
   )

3. Removed New Column “Format Date” as we can handle transformation directly in measure.

Solution attached
Ankit eDNA Forum - Previous Value identification.pbix (146.4 KB)

Thanks
Ankit

[Vishy]

Hi @Melissa & @ankit -

Thanks alot for the help. It is working perfectly in my case.

@ankit - Actually i had created sample data and provided the file. In my actual file i have included the date table and and liberty to the user to select the dates

Thank you guys once again.

Now going one step ahead, actually my abuser count calculation is not dynamic i.e. it is just counting rows based on the counts of abuser description. Similar to the lines of code what melissa had specified i did create a new measure for abuser count which is as below. This measure based on the date selection updates the abuser count suppose i did select December month it would count the abuser from December and gives that value and accordingly updated abuse timeline manager as well.
Abuse timeline =
VAR myID = SELECTEDVALUE(Sheet1[id])
VAR myDescr = SELECTEDVALUE(Sheet1[Abuser Descr])
var mydate = SELECTEDVALUE(Sheet1[Date])
VAR myTable =
FILTER( ALL( Sheet1 ) ,
Sheet1[id] = myID &&
Sheet1[Abuser Descr] = myDescr && Sheet1[Date] <= mydate
)

VAR myTable1 =
FILTER( ALL( Sheet1 ) ,
Sheet1[id] = myID &&
Sheet1[Abuser Descr] = myDescr
)

RETURN
IF(NOT(HASONEFILTER(Sheet1[Date])),IF( HASONEVALUE( Sheet1[id] ),
CALCULATE(
CONCATENATEX(
SELECTCOLUMNS( myTable1 ,
“Format Date” ,
[Format Date]
),
[Format Date] ,
", "
)
),
BLANK()
),
IF( HASONEVALUE( Sheet1[id] ),
CALCULATE(
CONCATENATEX(
SELECTCOLUMNS( myTable ,
“Format Date” ,
[Format Date]
),
[Format Date] ,
", "
)
),
BLANK()
))

Abuser Count =

Var Mydate = SELECTEDVALUE(Sheet1[Date])

var Myphone = SELECTEDVALUE(Sheet1[Phone Number])

var MyAbuser = SELECTEDVALUE(Sheet1[Abuser Descr])

Var mytable = FILTER(ALL(Sheet1),Sheet1[Date] <= Mydate && Sheet1[Phone Number] = Myphone && Sheet1[Abuser Descr] = MyAbuser)

return

IF(NOT(HASONEFILTER(Sheet1[Date])),

CALCULATE(COUNT(Sheet1[Abuser Descr]),ALLEXCEPT(Sheet1,Sheet1[Phone Number])

),CALCULATE(COUNTX(mytable,MyAbuser)))

@Melissa & @ankit - Kindly let me know if there is any short cut to handle this within your code. However the additions done by me are giving correct results.

Atttaching the pbix as well.
eDNA Forum - Previous Value identification - vishy.pbix (55.7 KB)