Forum Discussion

viralpatel21's avatar
viralpatel21
Helper II
4 years ago

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:

 

 

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

Need Help
Tutorial Requests

3 Replies

  • viralpatel21's avatar
    viralpatel21
    Helper II
    4 years ago
     
    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:

     

     

     

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      viralpatel21
      Helper II
      4 years ago

      Hi Anonymous 

       

      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