Count values depending on slicer date range and which of the 2 values is latest in range

[PanosSia]

I have a table[id], table[category] and table[date]

table[id] has values 0...999, and when let's say id = 10 then category can have rows with values 'a', 'b', 'c' with corresponding date for each row.

What i want and can't do for the sake of me is when 

1. choose in a slicer category = 'a'  

2. choose in a slicer of date a range let's say 1/1/2020 - 1/2/2020

and the difficult part is to count how many unique ids correspond in this range for category 'a' with the condition of 

if category = 'a' (date) > category = 'b' (date) (if there is category 'b' for id = 10  if there isnot it's still + 1) then +1 else 0

[ And if i chose for this particular range and the above statement was true then if i chose category = 'b' then it would be 0 to the count of distinct ids]

and likewise if choose in a slicer category = 'b' 

if category = 'b' (date) > category = 'a' (date) (if there is category 'a' for id = 10  if there is not it's still + 1) then +1 else 0

If it's not clear what i ask ofc i can elaborate more. Thank you!!

[v-tangjie-msft · Accepted solution]

Hi @PanosSia ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create measures. 

ount = 
var _slicer=SELECTEDVALUE('Table'[type])
var _date=CALCULATE(MAX('Table'[date]),FILTER(ALL('Table'),'Table'[type]=_slicer && 'Table'[id]=MAX('Table'[id])))
var _id=CALCULATE(MAX('Table'[id]),FILTER(ALL('Table'),'Table'[type]=_slicer && 'Table'[date]=_date ))
var _max=MAXX(FILTER(ALL('Table'),'Table'[id]=MAX('Table'[id])),'Table'[date])
var _count=IF(_date>=_max,1,0)
return _count

sum = SUMX(ALLSELECTED('Table'),'Table'[count])

flag = IF(VALUES('Table'[date]) in VALUES('CALENDAR'[Date]),1,0)

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[PanosSia]

As a more concrete example:

I choose for slicer 1/2/2020 15/2/2020 and category = 'a' with date 13/2/2020  [The slicer date is the table[date] which corresponds to the categories "date stamps"]

let's say for id=1 it has also category 'b' with date = 2/2/2020 

then the count +1 

and if choose category = 'b' then the count = 0

if i change the range to be 1/2/2020 - 5/2/2020 then only the category = 'b' is in and count = 1

and if choose category = 'a' then count = 0