How to group multiple variables into years?

[lifetimeapple]

Hi all!

I am trying to organize member data. I need to see how many active and inactive members we have year over year. These categories are complex.

"Active" members include people who have shopped that year (measured by one column that includes all dates they have ever shopped), and who are either active on their yearly payments (measured by one column that indicates the date of their last payment) or who are lifetime members who already reached the cap on their yearly payments (measured by one column with a dummy variable - 0 for not a lifetime member and 1 for a lifetime member).

"Inactive" members would include people who haven't shopped that year and/or are behind on their payments, and who have not cancelled their membership (measured by a couple columns with dummy variables - 0 for not cancelled and 1 for cancelled).

I want to see this data year over year - how many active and inactive members did we have in each year from 2010 to present? The only ways I can think of to accomplish this would be extremely time consuming and would have to be repeated at the start of every new year, which is not ideal.

Is there an (at least somewhat) easy way to set this up?

Thanks in advance!

[lifetimeapple]

The blog post shared by @amitchandak was exactly what I needed! Thank you!

[v-frfei-msft]

Hi @lifetimeapple ,

Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[Ashish_Mathur]

Hi,

Share a simple dataset (that can be pasted in MS Excel) and show the expected result.

[lifetimeapple]

I should note that I do have historical data for years 2010-2019 saved in an excel sheet from old reporting methods, so if there's an easy way to automatically add to that every new year and save it, instead of generating the historical yearly data from a Power BI query, I could do that instead. 

[amitchandak · Accepted solution]

The solution would in line with the solution in the blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

But will need more logics.

If possible please share a sample pbix file after removing sensitive information.Thanks.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601