Forum Discussion

Swathykorivi's avatar
Swathykorivi
Helper I
1 year ago
Solved

Need help with Power Bi calc

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 b...
Need Help
  • Swathykorivi's avatar
    Swathykorivi
    1 year ago

    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's avatar
    Ashish_Mathur
    1 year ago

    Hi,

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

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

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's avatar
Swathykorivi
Helper I
1 year ago

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]))