cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Boolean statement if item exists between tow dates

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.

Thanks!

2 ACCEPTED SOLUTIONS
Super User

@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)))

Community Support

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

5 REPLIES 5
Community Support

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

Helper I

Hi @amitchandak and @AlB

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!!

Helper I

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.

Super User

@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)))

Super User

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