Forum Discussion

MichaelBauld's avatar
MichaelBauld
Frequent Visitor
2 years ago
Solved

Operator or expression error in calculated table

Hi everyone,    I have a formula which I use to calculate the total number of active memberships at any given date using the start date and end date of the membership to work this out. This works f...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    MichaelBauld , Use the below DAX for new column

     

    DAX
    ActiveMembershipsTable =
    ADDCOLUMNS (
    'Calendar MASTER',
    "Total Household Count",
    VAR CurrentDate = 'Calendar MASTER'[Date]
    RETURN
    SUMX (
    FILTER (
    'Member_Warehouse',
    'Member_Warehouse'[validfrom] <= CurrentDate
    && (
    ISBLANK ( 'Member_Warehouse'[End Date using statecode])
    || 'Member_Warehouse'[End Date using statecode] > CurrentDate
    )
    ),
    1
    )
    )

bhanu_gautam's avatar
bhanu_gautam
Super User
2 years ago

MichaelBauld , Use the below DAX for new column

 

DAX
ActiveMembershipsTable =
ADDCOLUMNS (
'Calendar MASTER',
"Total Household Count",
VAR CurrentDate = 'Calendar MASTER'[Date]
RETURN
SUMX (
FILTER (
'Member_Warehouse',
'Member_Warehouse'[validfrom] <= CurrentDate
&& (
ISBLANK ( 'Member_Warehouse'[End Date using statecode])
|| 'Member_Warehouse'[End Date using statecode] > CurrentDate
)
),
1
)
)