Need help with Power Bi calc

[Swathykorivi]

Need help with power BI calc to calculate % change of member counts originated in an year over the max snapshot date in an year.

Below is the data and  calc i used , but i dont see any data when i bring , Actdate to rows and SnapshotYear to columns and YoY Member Change % to values in matrix table.

Actdate	Snapshotdate	Mcounts
2021	12/31/2021 0:00	236
2021	12/31/2022 0:00	217
2021	12/31/2023 0:00	206
2021	12/31/2024 0:00	195
2021	6/30/2025 0:00	190
2022	12/31/2022 0:00	140
2022	12/31/2023 0:00	129
2022	12/31/2024 0:00	121
2022	6/30/2025 0:00	118
2023	12/31/2023 0:00	80
2023	12/31/2024 0:00	76
2023	6/30/2025 0:00	74
2024	12/31/2024 0:00	44
2024	6/30/2025 0:00	42
2025	6/30/2025 0:00	18

YoY Member Change % =
VAR SelectedSnapshotYear = SELECTEDVALUE('DPMLY Snapshots'[SnapshotYear])
VAR SelectedCohort = SELECTEDVALUE('DPMLY Snapshots'[Actdate])

-- Current year member count
VAR CurrentMcount =
CALCULATE(
DISTINCTCOUNT('DPMLY Snapshots'[ACTID]),
FILTER(
'DPMLY Snapshots',
'DPMLY Snapshots'[SnapshotYear] = SelectedSnapshotYear &&
'DPMLY Snapshots'[Actdate] = SelectedCohort &&
'DPMLY Snapshots'[Snapshotdate] =
CALCULATE(
MAX('DPMLY Snapshots'[Snapshotdate]),
FILTER(
'DPMLY Snapshots',
'DPMLY Snapshots'[SnapshotYear] = SelectedSnapshotYear &&
'DPMLY Snapshots'[Actdate] = SelectedCohort
)
)
)
)

-- Previous year member count
VAR PrevMcount =
CALCULATE(
DISTINCTCOUNT('DPMLY Snapshots'[ACTID]),
FILTER(
'DPMLY Snapshots',
'DPMLY Snapshots'[SnapshotYear] = SelectedSnapshotYear - 1 &&
'DPMLY Snapshots'[Actdate] = SelectedCohort &&
'DPMLY Snapshots'[Snapshotdate] =
CALCULATE(
MAX('DPMLY Snapshots'[Snapshotdate]),
FILTER(
'DPMLY Snapshots',
'DPMLY Snapshots'[SnapshotYear] = SelectedSnapshotYear - 1 &&
'DPMLY Snapshots'[Actdate] = SelectedCohort
)
)
)
)

RETURN
DIVIDE(CurrentMcount - PrevMcount, PrevMcount)

[Swathykorivi · Accepted solution]

Thanks bhanu, i couldnt play with it much, when i used teh logic, i still couldnt get the calc correctly, i will work on it this week and will keep you posted.

[Ashish_Mathur · Accepted solution]

Hi,

Based on the table that you have shared, show the expected result very clearly.

[Ashish_Mathur · Accepted solution]

Hi,

Based on the table that you have shared, show the expected result very clearly.

[Swathykorivi]

Hi Ashish, below is the screenshout of the output ia m expecting

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Swathykorivi]

@Ashish_Mathur  : Thank you, this is such an easy solution.

[Ashish_Mathur]

You are welcome.

[v-menakakota]

Hi  @Swathykorivi  ,
Thanks for reaching out to the Microsoft fabric community forum. 

I would also take a moment to thank  @bhanu_gautam , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference
After trying if still you have problems on it, please feel free to let us know.   

Best Regards, 
Community Support Team  

[bhanu_gautam]

@Swathykorivi  Try using

DAX
YoY Member Change % =
VAR SelectedSnapshotYear = SELECTEDVALUE('DPMLY Snapshots'[SnapshotYear])
VAR SelectedCohort = SELECTEDVALUE('DPMLY Snapshots'[Actdate])

-- Current year member count
VAR CurrentMcount =
CALCULATE(
MAX('DPMLY Snapshots'[Mcounts]),
FILTER(
'DPMLY Snapshots',
'DPMLY Snapshots'[SnapshotYear] = SelectedSnapshotYear &&
'DPMLY Snapshots'[Actdate] = SelectedCohort &&
'DPMLY Snapshots'[Snapshotdate] =
CALCULATE(
MAX('DPMLY Snapshots'[Snapshotdate]),
FILTER(
'DPMLY Snapshots',
'DPMLY Snapshots'[SnapshotYear] = SelectedSnapshotYear &&
'DPMLY Snapshots'[Actdate] = SelectedCohort
)
)
)
)

-- Previous year member count
VAR PrevMcount =
CALCULATE(
MAX('DPMLY Snapshots'[Mcounts]),
FILTER(
'DPMLY Snapshots',
'DPMLY Snapshots'[SnapshotYear] = SelectedSnapshotYear - 1 &&
'DPMLY Snapshots'[Actdate] = SelectedCohort &&
'DPMLY Snapshots'[Snapshotdate] =
CALCULATE(
MAX('DPMLY Snapshots'[Snapshotdate]),
FILTER(
'DPMLY Snapshots',
'DPMLY Snapshots'[SnapshotYear] = SelectedSnapshotYear - 1 &&
'DPMLY Snapshots'[Actdate] = SelectedCohort
)
)
)
)

RETURN
IF(
NOT ISBLANK(CurrentMcount) && NOT ISBLANK(PrevMcount),
DIVIDE(CurrentMcount - PrevMcount, PrevMcount, 0),
BLANK()
)

[Swathykorivi]

@bhanu_gautam : this is the error i am getting. The MAX function only accepts a column reference as an argument.
below is the logic i am using to achive member counts .

Membercount = CALCULATE(DISTINCTCOUNT('DPMLY Snapshots'[ACTid]),'DPMLY Snapshots'[Member Flag]>=1)

Member Flag = CALCULATE(DISTINCTCOUNT('DPMLY Snapshots'[ACTid]), ALLEXCEPT('DPMLY Snapshots','DPMLY Snapshots'[Snapshot_DateID],'DPMLY Snapshots'[ACTid]),
'DPMLY Snapshots'[AccountStatus]="Open",'DPMLY Snapshots'[Prodcode]="1" ,ISBLANK('DPMLY Snapshots'[ChargeOffDate]),ISBLANK('DPMLY Snapshots'[CloseDate]))

[Swathykorivi · Accepted solution]

Thanks bhanu, i couldnt play with it much, when i used teh logic, i still couldnt get the calc correctly, i will work on it this week and will keep you posted.