Dax Formula for getting result based on sumx, filter and related

[cakarunkumar]

I have the following tables setup in the power BI 1) Day book - columns include date, ledger name, debit, credit, 2) running trial balance - columns include date, opening balance, Debit, Credit, Closing Balance , 3) Calendar - Date, Day. The relationship between these tables are setup as follows: Running trial balance to day book - 1 to many, day book to calendar- many to 1. Now i have a page slicer setup in my report page based on calendar month. i am trying to write a dax code to sum the total of debit and credits from the day book by looking up the ledger name and filtering upto the max date as available in the slicer. sumx with related and filter formula are not working. kindly suggest a dax code

[amitchandak]

@cakarunkumar , If you join with a date table and simply SUM, it will work.

Or you will measure like

CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[opening balance]),filter(all(date),date[date] <=max(date[date]))) +
CALCULATE(SUM(Table[Credit]),filter(all(date),date[date] <=max(date[date]))) -
CALCULATE(SUM(Table[Debit]),filter(all(date),date[date] <=max(date[date])))

Power BI Inventory On Hand
Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw