Distinct Count of Last Status over time

[andreabrezmes]

Hi everyone,

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

The data I have is as follows: 

FACT TABLE (Table1):

Id	Status	Date
1	Apples	1/10/2018
1	Bananas	1/20/2018
1	Grapes	3/18/2018
2	Bananas	2/18/2018
2	Apples	4/10/2018
2	Grapes	5/10/2018
3	Grapes	1/10/2018
3	Bananas	3/12/2018

Calendar:

Date	YearMonth
1/1/2018	Jan-18
…	…
31/1/2018	Jan-18
…	…
30/05/2018	May-18

The result I want to obtain is the following: 

	Jan-18	Feb-18	Mar-18	Apr-18	May-18
Apples	0	0	0	1	0
Bananas	1	2	2	1	1
Grapes	1	1	1	1	2

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.

[v-danhe-msft]

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:

Regards,

Daniel He

[andreabrezmes]

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.