Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Balance Sheet and P&L Smart Measure

Hello friends,

I want to create one Account Dimention that will allow me to see both BalanceSheet accounts and P&L accounts.

For Balance sheet - I need the measure to show the "end-of-period" balance, but for the P&L - I need it to show the simple SUM() of all transations of that period.

I need it to be one measure. So far - I've managed.

The tricky part is that the P&L net amount has to be added to the Balance sheet "Accumulated Profits" section. Any ideas on how to achieve this?

Thanks!

Michael

 

 

Need Help

1 Reply

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Anonymous

     

    Based on your screenshot, it's a report, not a dimenison table. In this scenario, your Account Dimension should only contain account information. For those Profits/Sales data, it should be included in a fact table which connnects Account Dimension and Date Dimension. 

     

    Since your Balance Sheet and P&L account are under different granularity, you should create another P&L account Table. And it's not possible to achieve via only one measure. DAX can't use current slicing member as condition in calculation. You have to separate the P&L total amount into antoher measure.

     

    Regards,