Forum Discussion

xonder's avatar
xonder
Icon for Helper I rankHelper I
5 years ago
Solved

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!

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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's avatar
    v-deddai1-msft
    5 years ago

    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

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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 

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

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

  • xonder's avatar
    xonder
    Icon for Helper I rankHelper I
    5 years ago

    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!

  • xonder's avatar
    xonder
    Icon for Helper I rankHelper I
    5 years ago

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

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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