sum - Power BI - Dax Opening Balance Measure by branch, account and subaccount

Question

Aplogies for a very noob question, but I am struggling to come up with the correct measures. I have a general ledger table 43,000 rows (FCT_GL_Details), which has transactions that I want to consolidate into a trail balance (FCT_GL_TB) grouped by Branch, Account, Subaccount, finYear in "yyyy" format, finMonth in "mm" , PeriodID (being FinYearFinMonth in "yyyymm" and then sum the NettAmt (which I have called as NettAmt). I have created a referenced table and groupd by the above. What I now need is two opening balances, one a life-to-date (LTD_Opening), and year-to-date (YTD_Opening) which is based on same financial year.

The excel equivalent would be:

LTD_Opening = sumifs([NettAmt],[Periodid],"<"&[@[PeriodID]],[Branch],[@[Branch]],[Account],[@[Account]],[Subaccount],[@[Subaccount]])
and YTD would include ... ",[FinYear],[@[FinYear]])

I am struggling with the filtering how to switch from Excel convention of prefixing the row value with "@".

Example is as follows:

Many thanks in advance for your help with this.

David

question from:https://stackoverflow.com/questions/65948428/power-bi-dax-opening-balance-measure-by-branch-account-and-subaccount

Answer 1

Although I have some confusion on your opening balance consideration, you can achieve your required output with couple of Custom Column and Measures given below-

First of all lets create a Date Custom Column to check dates using value from FinYear and FinMonth for all months as below-

first_date_of_month = DATE(your_table_name[finyear],your_table_name[finmonth],1)

Now create this below 2 more Custom Column where from we can check the row belongs to Opening date regards to corresponding Branch and FinYear.

is_initial_opening_of_branch = 

var current_row_branch = your_table_name[branch]
var current_row_first_date_of_month = your_table_name[first_date_of_month]

var check_first_balance_of_branch =
CALCULATE(
    COUNTROWS(your_table_name),
    FILTER(
        ALL(your_table_name),
        your_table_name[branch] = current_row_branch
        && your_table_name[first_date_of_month] <= current_row_first_date_of_month
    )
)

return IF(check_first_balance_of_branch = 1,1,0)

is_initial_opening_of_finyear = 

var current_row_branch = your_table_name[branch]
var current_row_finyear = your_table_name[finyear]
var current_row_first_date_of_month = your_table_name[first_date_of_month]

var check_first_balance_of_branch_finyear =
CALCULATE(
    COUNTROWS(your_table_name),
    FILTER(
        ALL(your_table_name),
        your_table_name[branch] = current_row_branch
        && your_table_name[finyear] = current_row_finyear
        && your_table_name[first_date_of_month] <= current_row_first_date_of_month
    )
)

return IF(check_first_balance_of_branch_finyear = 1,1,0)

Now lets create your required 2 measure as below-

ltd_opening = 

var current_row_branch = MIN(your_table_name[branch])
var current_row_first_date_of_month = MIN(your_table_name[first_date_of_month])

return 
CALCULATE(
    sum(your_table_name[nett]),
    FILTER(
        ALL(your_table_name),
        your_table_name[branch] = current_row_branch
        && your_table_name[first_date_of_month] <= current_row_first_date_of_month
        && your_table_name[is_initial_opening_of_branch] <> 1 
    )
) + 0

ytd_opening = 

var current_row_branch = MIN(your_table_name[branch])
var current_row_finyear = MIN(your_table_name[finyear])
var current_row_first_date_of_month = MIN(your_table_name[first_date_of_month])

return 
CALCULATE(
    sum(your_table_name[nett]),
    FILTER(
        ALL(your_table_name),
        your_table_name[branch] = current_row_branch
        && your_table_name[finyear] = current_row_finyear
        && your_table_name[first_date_of_month] <= current_row_first_date_of_month
        && your_table_name[is_initial_opening_of_finyear] <> 1 
    )
) + 0

Here is the final output-