Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
viralpatel21
Helper II
Helper II

Creating an opening and closed table - members

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: 

MonthJanFebMar
Opening balance891490109230
Active Registration19320101541
Active Subscription-102-1790-1290
Closing balance901092309481

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

viralpatel21_0-1631291130783.png

 

 

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
viralpatel21_1-1631291130822.png

 

Please could you help! been struggling for a week!

 

Thanks

Viral

3 REPLIES 3
viralpatel21
Helper II
Helper II

 
hello,
 

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

MonthJanFebMar
Opening balance891490109230
Active Registration19320101541
Active Subscription-102-1790-1290
Closing balance901092309481

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

viralpatel21_1-1631521461053.png

 

 

 

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
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

MonthJanFebMar
Opening balance891490109230
Active Registration19320101541
Active Subscription-102-1790-1290
Closing balance901092309481

 

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

 

Hopefully this made some sense.

 

thanks

viral

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors