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 bring , Actdate to rows and SnapshotYear to columns and YoY Member Change % to values in matrix table.

 

ActdateSnapshotdateMcounts
202112/31/2021 0:00236
202112/31/2022 0:00217
202112/31/2023 0:00206
202112/31/2024 0:00195
20216/30/2025 0:00190
202212/31/2022 0:00140
202212/31/2023 0:00129
202212/31/2024 0:00121
20226/30/2025 0:00118
202312/31/2023 0:0080
202312/31/2024 0:0076
20236/30/2025 0:0074
202412/31/2024 0:0044
20246/30/2025 0:0042
20256/30/2025 0:0018

 

 

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)

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.

9 Replies

  • 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

      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.

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

  • v-menakakota's avatar
    v-menakakota
    Community Support
    1 year ago

    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  

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

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