cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
andreabrezmes Frequent Visitor
Frequent Visitor

Distinct Count of Last Status over time

Hi everyone,

 

I'm struggling trying to obtain the last status for an id. 

The data I have is as follows: 

FACT TABLE (Table1):

IdStatusDate
1Apples1/10/2018
1Bananas1/20/2018
1Grapes3/18/2018
2Bananas2/18/2018
2Apples4/10/2018
2Grapes5/10/2018
3Grapes1/10/2018
3Bananas3/12/2018

 

Calendar:

DateYearMonth
1/1/2018Jan-18
31/1/2018Jan-18
30/05/2018May-18

 

The result I want to obtain is the following: 

 

 Jan-18Feb-18Mar-18Apr-18May-18
Apples00010
Bananas12211
Grapes11112

 

I'm able to obtain the last date:

 

TestStep_1 = VAR myDate = MAX('Calendar'[Date])
RETURN(CALCULATE(MAX(Table1[Date]), FILTER(ALLEXCEPT(Table1, Table1[Id]),Table1[Date]<myDate)
)
)
 
and status:
 
TestStep_2 =
VAR testdate = Table1[TestStep_1]
RETURN(CALCULATE(MAX(Table1[Status]), FILTER(ALLEXCEPT(Table1, Table1[Id]), Table1[Date] = testdate)))
 
Anybody has ever face this issue?
Thanks a lot.
 
 

 

 

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: Distinct Count of Last Status over time

Hi @andreabrezmes ,

Could you please offer more information about the logic of distinct count of last status over time?

For status "Apples", it is shown in 2018/1/10, but it seemed not show in your result table:

1.PNG

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
andreabrezmes Frequent Visitor
Frequent Visitor

Re: Distinct Count of Last Status over time

Hi Daniel,

 

I would like to obtain the last status by month.

 

Last status for id 1 is "bananas" for january, so "apples" becomes 0. In February we have 2 id's with status "bananas" and in March id 1 becomes "grapes" and id 2 and 3 are "bananas".

 

Thanks,

 

Andrea.