Creating an opening and closed table - members

[viralpatel21]

Hi

Not sure but my previous post was marked as spam? This is not a spam as im looking for some advice 🙂

I was hoping if you could help me. I have been asked to create this in powerbi for each month: 

Month	Jan	Feb	Mar
Opening balance	8914	9010	9230
Active Registration	193	2010	1541
Active Subscription	-102	-1790	-1290
Closing balance	9010	9230	9481

 

However I am not sure if its my closing balance or my Opening balance measure is correct. This is what i am getting:

Here are the measure i used:

Opening Balance: 

Opening balance = OPENINGBALANCEMONTH([Closing balance],FeeDate[Date])

Active Registration: 

Active Registrations =

CALCULATE (

DISTINCTCOUNT ( contacts[contactid] ),

FILTER(contacts, contacts[au_memberstatus] = 827080000),

USERELATIONSHIP(FeeDate[Date],contacts[createdon])

)

Active Subscription:

Active Subscriptions =

CALCULATE (

[Active Registrations] * -1,

FILTER ( contacts, contacts[_au_activesubscription_value] <> BLANK() ),

USERELATIONSHIP(FeeDate[Date],au_subscriptions[CreatedDate])

)

Closing Balance:

Closing balance =

Var running = IF (

[Active Registrations] <> BLANK (),

CALCULATE (

[Active Registrations],

FILTER ( ALL ( FeeDate[Date] ), FeeDate[Date] <= MAX ( FeeDate[Date] ) )

)

)

return

running + [Active Subscriptions]

Here is the relationship diagram

Please could you help! been struggling for a week!

Thanks

Viral

[viralpatel21]

I was hoping if you could help me. I have been asked to create this in powerbi for each month: 

Month	Jan	Feb	Mar
Opening balance	8914	9010	9230
Active Registration	193	2010	1541
Active Subscription	-102	-1790	-1290
Closing balance	9010	9230	9481

 

However I am not sure if its my closing balance or my Opening balance measure is correct. This is what i am getting:

Here are the measure i used:

Opening Balance: 

Opening balance = OPENINGBALANCEMONTH([Closing balance],FeeDate[Date])

Active Registration: 

Active Registrations =

CALCULATE (

DISTINCTCOUNT ( contacts[contactid] ),

FILTER(contacts, contacts[au_memberstatus] = 827080000),

USERELATIONSHIP(FeeDate[Date],contacts[createdon])

)

Active Subscription:

Active Subscriptions =

CALCULATE (

[Active Registrations] * -1,

FILTER ( contacts, contacts[_au_activesubscription_value] <> BLANK() ),

USERELATIONSHIP(FeeDate[Date],au_subscriptions[CreatedDate])

)

Closing Balance:

Closing balance =

Var running = IF (

[Active Registrations] <> BLANK (),

CALCULATE (

[Active Registrations],

FILTER ( ALL ( FeeDate[Date] ), FeeDate[Date] <= MAX ( FeeDate[Date] ) )

)

)

return

running + [Active Subscriptions]

Here is the relationship diagram

Please could you help! been struggling for a week!

Thanks

Viral

[viralpatel21]

hello,

I was hoping if you could help me. I have been asked to create this in powerbi for each month: 

Month	Jan	Feb	Mar
Opening balance	8914	9010	9230
Active Registration	193	2010	1541
Active Subscription	-102	-1790	-1290
Closing balance	9010	9230	9481

 

However I am not sure if its my closing balance or my Opening balance measure is correct. This is what i am getting:

Here are the measure i used:

Opening Balance: 

Opening balance = OPENINGBALANCEMONTH([Closing balance],FeeDate[Date])

Active Registration: 

Active Registrations =

CALCULATE (

DISTINCTCOUNT ( contacts[contactid] ),

FILTER(contacts, contacts[au_memberstatus] = 827080000),

USERELATIONSHIP(FeeDate[Date],contacts[createdon])

)

Active Subscription:

Active Subscriptions =

CALCULATE (

[Active Registrations] * -1,

FILTER ( contacts, contacts[_au_activesubscription_value] <> BLANK() ),

USERELATIONSHIP(FeeDate[Date],au_subscriptions[CreatedDate])

)

Closing Balance:

Closing balance =

Var running = IF (

[Active Registrations] <> BLANK (),

CALCULATE (

[Active Registrations],

FILTER ( ALL ( FeeDate[Date] ), FeeDate[Date] <= MAX ( FeeDate[Date] ) )

)

)

return

running + [Active Subscriptions]

Thanks

Viral

[viralpatel21]

I was hoping if you could help me. I have been asked to create this in powerbi for each month: 

Month	Jan	Feb	Mar
Opening balance	8914	9010	9230
Active Registration	193	2010	1541
Active Subscription	-102	-1790	-1290
Closing balance	9010	9230	9481

 

However I am not sure if its my closing balance or my Opening balance measure is correct. This is what i am getting:

Here are the measure i used:

Opening Balance: 

Opening balance = OPENINGBALANCEMONTH([Closing balance],FeeDate[Date])

Active Registration: 

Active Registrations =

CALCULATE (

DISTINCTCOUNT ( contacts[contactid] ),

FILTER(contacts, contacts[au_memberstatus] = 827080000),

USERELATIONSHIP(FeeDate[Date],contacts[createdon])

)

Active Subscription:

Active Subscriptions =

CALCULATE (

[Active Registrations] * -1,

FILTER ( contacts, contacts[_au_activesubscription_value] <> BLANK() ),

USERELATIONSHIP(FeeDate[Date],au_subscriptions[CreatedDate])

)

Closing Balance:

Closing balance =

Var running = IF (

[Active Registrations] <> BLANK (),

CALCULATE (

[Active Registrations],

FILTER ( ALL ( FeeDate[Date] ), FeeDate[Date] <= MAX ( FeeDate[Date] ) )

)

)

return

running + [Active Subscriptions]

Thanks

Viral

[v-yiruan-msft]

Hi @viralpatel21 ,

Could you please provide some sample data for table contacts, FeeDate & au_subscriptions and information about their relationships? The screenshot is a bit blurry, so I can't see it clearly. Also, please provide the logic for calculating the opening balance and closing balance.
Opening balance=?
Closing balance=?
I found some links that calculate opening balance and closing balance. You can refer to them to get the results you want.

DAX Closing and Opening Balances

Opening Balance DAX | Closing Balance DAX

Closing and opening balance with cashflow

Best Regards

[viralpatel21]

Hi @v-yiruan-msft 

Thank you for replying to my query. It will be very difficult in providing a sample size for contacts table and au_subscription. However the FeeDate table has got 2 column: Date and Months

Date = Every date from 1st of jan 2020 till today in the format of 01/01/2020

Months: just for each date what the month is i.e. 01/01/2021 = Jan20

I beleive my opening and closing balance is wrong but this is what i want to acheive: 

Opening balance = This should be the closing balance of the previous month. i.e:

Jan closing balance is 9010, then Feb opening balance is 9010

Month	Jan	Feb	Mar
Opening balance	8914	9010	9230
Active Registration	193	2010	1541
Active Subscription	-102	-1790	-1290
Closing balance	9010	9230	9481

Closing balance =  Opening balance of the month + Active registration - Active Subscription

Hopefully this made some sense.

thanks

viral