cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Microsoft
Microsoft

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.
Highlighted
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.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors