Boolean statement if item exists between tow dates

[xonder]

I collect data of advertised vehicles at irregulars intervals. Each time I collect data, I get a snapshot of what is currently being advertised along with a time stamp.

05.11.2020 is the first date of my data set and 08.11.2020 is the last date of my data set

Date	ID
05.11.2020	123456
05.11.2020	789011
06.11.2020	123456
06.11.2020	789011
06.11.2020	121314
06.11.2020	151617
07.11.2020	123456
07.11.2020	789011
07.11.2020	121314
07.11.2020	151617
08.11.2020	123456
08.11.2020	121314

I would like to  be able to answer the question "How many vehicles have been sold between dd.mm.yyyy and DD.MM.YYYY?"

A vehicle is sold if last date of a given ID is smaller than the last date of my data set.

If dd.mm.yyyy = 05.11.2020 and DD.MM.YYYY = 08.11.2020 then 2 vehicles, 789011 and 151617 have been sold.

If I change my page filter so that dd.mm.yyyy = 05.11.2020. and DD.MM.YYYY = 07.11.2020 then 0 vehicles have been sold.

How can I create a TRUE/FLASE measure that tells me wheather an ID has been sold and make sure that this measure changes depending on the date range selected in the page/chart filters.

here is by pbix file 

Thanks!

[amitchandak · Accepted solution]

@xonder , Create a measure like this and try with ID

measure =
var _max = maxx(allselected(Table), Table[Date])
var _tab =summarize(filter(Table, Table[Date] =_max),Table[ID])
return
calculate( count(Table[ID]), filter( Table, Table[Date] <_max && not(Table[ID] in _tab)))

[v-deddai1-msft · Accepted solution]

Hi @xonder ,

You can create two measure for counting ID that are marked "Sold" and those marked "For Sale":

ID For Sale  = CALCULATE(COUNT('Table'[ID]),FILTER('Table',[Status] = "For Sale"))
ID Sold  = CALCULATE(COUNT('Table'[ID]),FILTER('Table',[Status] = "Sold"))

Put them in values  and it will show as legends.

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

Best Regards,

Dedmon Dai

[xonder]

Hi @amitchandak and @AlB 

I have made some progress.

I have created a the follwing measure

Status = IF(MAXX(azw,azw[date])< LASTDATE(ALL(azw[date])), "Sold", "For Sale")

That allows me to get the current status of each ID as shown below.

 

However, I can't seem to use the measure "Status" in a chart as an Axis or Legend.

How can I use this measure to show the number (COUNT) of ID that are marked "Sold" and those marked "For Sale"?

Thank you!!

[xonder]

Thank you @amitchandak  and @AlB  for your replies. These however do not solve my issue.

Is there a way to simply have a table with each ID and a measure that tell me whether a ID is Sold or Not Sold?

An ID is sold if it no longer appears in the latest data extract I do.

Thanks for your help!

[amitchandak · Accepted solution]

@xonder , Create a measure like this and try with ID

measure =
var _max = maxx(allselected(Table), Table[Date])
var _tab =summarize(filter(Table, Table[Date] =_max),Table[ID])
return
calculate( count(Table[ID]), filter( Table, Table[Date] <_max && not(Table[ID] in _tab)))

[AlB]

Hi @xonder 

1. Place ID in the a table visual

2. Create this measrue and place it in the visual

Measure = 
MAX(Table1[Date]) = CALCULATE(MAX(Table1[Date]), ALL(Table1[ID]))

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers