cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ossy81
Helper I
Helper I

Get first and last status of a member

Working on a report to show how many members moved between different statuses. How many from:

A -> B

B->A

A->C

within a selected period. So it could be that member went from A->B and than from B->C but for the report it should be registered as a member moved from A->C

 

I used this formula:

 VAR MinDate = CALCULATE ( MIN ( 'F - Transaction ML'[d_date] )
, NOT ( ISBLANK ( 'F - Transaction ML'[d_member] ) )
, ALLSELECTED ( 'F - Transaction ML'[d_date] ) )

VAR
Weekstartdate = MinDate - WEEKDAY ( MinDate, 2 ) + 1

VAR
FirstMemberStatus = CALCULATE ( MIN ( 'F - Transaction ML'[Member Status] )
, ALLEXCEPT ( 'F - Transaction ML', 'D - Calendar' )
, FILTER ( 'F - Transaction ML'
, 'F - Transaction ML'[d_date] >= Weekstartdate
) )

RETURN
FirstMemberStatus

 And created a similar one for the last status, but then with MAX. When showing it on a member level it works fine, but i need to know "total number of members"/count distinct from status A->B , A->C etc...

 

Who could guide me how to solve this?I need somehow this calc to be always on a member level

3 REPLIES 3
yingyinr
Community Support
Community Support

Hi @Ossy81 ,

Do you wnat to get the number of members which the status switch from one to another? In order to get a better understanding on your requirement and give you a suitable solution, could you please provide some sample data in the table 'F - Transaction ML' (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. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

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

I'm afraid this is not going to work 😞 We want to know how many members moved van status A-> B and from A-> C

 

I already had a result which shows per member the 1st and the last status:

Ossy81_0-1668508381252.png

 

But when i do a count distinct by these statuses it doesn't work:

Ossy81_1-1668508424693.png

 

 

So the result what i want is:

 

Ossy81_2-1668508537728.png

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors