Forum Discussion
Solved
No results
Hi, I am new in here and in Dax. I couln't find a similar question, so hopefully someone can help. I am trying to achieve the same as Excel: countif(B:B;B2;A:A;D2). This is what I am doing: Count...
Hi 3s
Have tried this in desktop with your sample data. Please let me know the exact requirement/logic if it does not work.
Hi,
These calculated column formulas work
=CALCULATE(COUNTROWS(Data),FILTER(Data,Data[member]=EARLIER(Data[member])&&Data[year]=EARLIER(Data[year+1])))+0=CALCULATE(COUNTROWS(Data),FILTER(Data,Data[member]=EARLIER(Data[member])&&Data[year]=EARLIER(Data[year-1])))+0Hope this helps.
Now I am getting results. Unfortunately it is not wat I am looking for.
I want to calculate the number of starting and stopping members.
The columns 'year+1' and 'year-1' are calculated with column 'year'.
The columns 'also member following year' and 'was member last year' are the columns I am looking for a DAX-formula.
In Excel it would be for 'also member following year': countifs(B:B;B2;A:A;C2)
And for 'was member last year': countifs(B:B;B2;A:A;E2)
If there are other options I am interested as well.
| Column A | Column B | Column C | Column D | Column E | Column F |
| year | member | year+1 | also member following year | year-1 | was member last year |
| 2016 | K58PR5Q | 2017 | 0 | 2015 | 0 |
| 2018 | K58PR5Q | 2019 | 1 | 2017 | 0 |
| 2019 | K58PR5Q | 2020 | 1 | 2018 | 1 |
| 2020 | K58PR5Q | 2021 | 1 | 2019 | 1 |
| 2021 | K58PR5Q | 2022 | 1 | 2020 | 1 |
| 2022 | K58PR5Q | 2023 | 1 | 2021 | 1 |
| 2023 | K58PR5Q | 2024 | 1 | 2022 | 1 |
| 2024 | K58PR5Q | 2025 | 1 | 2023 | 1 |
| 2025 | K58PR5Q | 2026 | 0 | 2024 | 1 |
| 2022 | K65RR1W | 2023 | 1 | 2021 | 0 |
| 2023 | K65RR1W | 2024 | 1 | 2022 | 1 |
| 2024 | K65RR1W | 2025 | 1 | 2023 | 1 |
| 2025 | K65RR1W | 2026 | 0 | 2024 | 1 |
| 2018 | K36GO4B | 2019 | 0 | 2017 | 0 |
| 2020 | K36GO4B | 2021 | 0 | 2019 | 0 |
| 2022 | K36GO4B | 2023 | 0 | 2021 | 0 |
| 2024 | K36GO4B | 2025 | 0 | 2023 | 0 |
| 2019 | K33AM4I | 2020 | 1 | 2018 | 0 |
| 2020 | K33AM4I | 2021 | 1 | 2019 | 1 |
| 2021 | K33AM4I | 2022 | 0 | 2020 | 1 |
| 2025 | K33AM4I | 2026 | 0 | 2024 | 0 |
| 2022 | K33QZ4N | 2023 | 0 | 2021 | 0 |
| 2023 | K33RX7X | 2024 | 0 | 2022 | 0 |
| 2025 | K34AN4J | 2026 | 0 | 2024 | 0 |
| 2025 | K35HR4A | 2026 | 0 | 2024 | 0 |
| 2024 | K52XJ8M | 2025 | 0 | 2023 | 0 |
| 2021 | K33WA1O | 2022 | 0 | 2020 | 0 |
| 2017 | K32SV1D | 2018 | 0 | 2016 | 0 |
| 2016 | K34DF6S | 2017 | 0 | 2015 | 0 |
| 2019 | K35UQ2U | 2020 | 0 | 2018 | 0 |
- Ashish_Mathur
Super User
Hi,
These calculated column formulas work
=CALCULATE(COUNTROWS(Data),FILTER(Data,Data[member]=EARLIER(Data[member])&&Data[year]=EARLIER(Data[year+1])))+0=CALCULATE(COUNTROWS(Data),FILTER(Data,Data[member]=EARLIER(Data[member])&&Data[year]=EARLIER(Data[year-1])))+0Hope this helps.