group-bypowerbipowerquery
Power BI - Calculate number of consecutive years by group
I have a grouped table of customers and years. I would like to add a column to identify how many consecutive years the member has been a customer.
| ID | Year |
|---|---|
| A | 2019 |
| A | 2020 |
| A | 2021 |
| A | 2022 |
| B | 2019 |
| B | 2021 |
| B | 2022 |
The added formula should calculate the number of consecutive years as a customer, resetting when a year is skipped.
| ID | Year | Yrs_Cust |
|---|---|---|
| A | 2019 | 0 |
| A | 2020 | 1 |
| A | 2021 | 2 |
| A | 2022 | 3 |
| B | 2019 | 0 |
| B | 2021 | 0 |
| B | 2022 | 1 |
Solution
Another alternative.
Add a single column with the following code:
= Table.AddColumn(#"Changed Type", "Custom", each [
a = Table.SelectRows(#"Changed Type", (x)=> [ID]= x[ID]),
b = List.Select( a[Year], (x)=> x <= [Year] ),
c = List.Reverse(b),
d = List.Accumulate(c, [now = [Year], run = 0],(state, current)=> [now = state[now]-1, run = if current = state[now] then state[run] + 1 else state[run] ])[run]-1
][d])
- Group-wise total in Pandas
- SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by
- How to use Oracle's LISTAGG function with a unique filter?
- enumerate items in each group
- how to use inequality filter in pivotby formula on aggregated values, not table data
- What row gets selected when you GROUP BY without aggregate function?
- Drop duplicates using pandas groupby
- Select first row in each group using GROUP BY
- Plot pandas groupby boxplot and dataframe plot in the same figure
- How to get highest value from xml element in C#?
- Pandas groupby with tag-style list
- How to concatenate strings of a string field in a PostgreSQL 'group by' query?
- How to use vectorized calculations in pandas to find out where a value or category is changing with corrected first row?
- Changing a pandas dataframe format into another format?
- How do I figure cumulative distribution within groups (as cume_dist does) but separate tied values in R?
- Get last row that satisfies a condition using pandas groupby
- Pandas equivalent of GROUP BY HAVING in SQL
- How to use unboundedPreceding, unboundedFollowing and currentRow in rowsBetween in PySpark
- Using Count to find the number of occurrences
- Quick way to create hundreds of new columns in Pandas dataframe
- Get only the first and last rows of each group with pandas
- count distinct values while left joining
- SQL for returning number of days since last availability, grouped by category?
- spark dataframe groupping does not count nulls
- Why does summing data grouped by df.iloc[:, 0] also sum up the column names?
- Appending column values from one dataframe to another as a list
- Calculate percentage of flag grouped by another column
- Find source and final destination using sql
- MySQL GROUP BY with MIN - incorrect column data
- mysql multiple group by