Skip to main content
cancel
Showing results for 
Search instead 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

Reply
xonder
Helper I
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

 

DateID
05.11.2020123456
05.11.2020789011
06.11.2020123456
06.11.2020789011
06.11.2020121314
06.11.2020151617
07.11.2020123456
07.11.2020789011
07.11.2020121314
07.11.2020151617
08.11.2020123456
08.11.2020121314

 

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!

2 ACCEPTED SOLUTIONS
amitchandak
Super User
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)))

View solution in original post

v-deddai1-msft
Community Support
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

View solution in original post

5 REPLIES 5
v-deddai1-msft
Community Support
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

xonder
Helper I
Helper I

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.

 

image.png 

 

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
Helper I
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.

 

Thanks for your help!

amitchandak
Super User
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)))

AlB
Super User
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 

SU18_powerbi_badge

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.