Filtered Measure

DAX DAX Calculations
1

I am trying to develop a financial report that shows Balance sheet and Income Statement information. The problem is that I do not have separate tables for Assets, LIabilities, Revenue, Cost of Sales, Expenses. It seems that I will need to create measures that calculate these fields. However, there is no column for me to filter these areas only GL Codes. For example Revenue entries are coded to GL codes between 8000-8999.

How do I write a DAX Formula that returns the sum of a column ONLY if the GL Code falls between 8000 and 8999?

Revenue.pbix (3.0 MB)

2

@jmolina,

The key here is changing your Account Code field from type Text to type Whole Number. Once that is done, this measure should do the trick:

Actual Sum 8000s Filtered = 

CALCULATE(
    [Total Actuals],
    FILTER(
        'GL Transactions',
        'GL Transactions'[Account Code] > 7999 && 
        'GL Transactions'[Account Code] < 9000
    )
)

Hope this is helpful. Full solution file below.

P.S. If you’re going to be doing substantial analysis with this data, it likely would be worth the time to restructure your flat file into a relational financial data model via Power Query. Here are a couple of videos that may be helpful in that regard:

https://forum.enterprisedna.co/t/data-setup-tips-for-financial-reporting-power-bi/758

3

@jmolina

The correct method is to link your journal fact tables to a Chart of Accounts dimension table.

The accounts dimension table normally has each account in your chart marked with an “Account Class” like asset, liability, income etc… which can then be grouped into Balance sheet or Profit and Loss accounts.

It’s then easy to write the measures that make up the balance sheet like Assets, Liabilities, Equity etc. Note also sub-account classifications in your accounts can also exist like accounts payable, accounts receivable, payroll etc.

Here is a typical setup

image
image.png1306Ă—911 76.8 KB

1 Like
4

Hi @jmolina, we’ve noticed that no response has been received from you since December 27, 2019. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!