Balance Sheet YTD

Hi ljimenezvega84 ,

 

Since your numerical column represents cumulative life to date balance (i.e., normal balance sheet balance), you can create a measure like below to ensure that the last balance is picked up.

 

Balance as of Period = 

// 1. Get the last date of the current period (e.g., "2024-May-31")
VAR _LastDateInContext = 
    MAX ( Dim_Date[Date] )

// 2. Find the last *actual* data point in the *entire* fact table
VAR _LastOverallSnapshotDate = 
    CALCULATE(
        MAX(Fact_GL[Period]),
        ALL(Fact_GL) 
    )

// 3. Check if a date filter is active on the visual (i.e., this is the matrix)
VAR _IsDateContextFiltered = 
    ISFILTERED(Dim_Date[Date]) || 
    ISFILTERED(Dim_Date[YearMonth])

// 4. Main Logic: Iterate per account to fix totals
RETURN
SUMX(
    // *** THIS IS THE FIX ***
    // Get the list of GL Accounts, but remove any date filters first.
    // This stops the iterator from being blank.
    CALCULATETABLE(
        VALUES(Fact_GL[GL Account]),
        REMOVEFILTERS(Dim_Date)
    ),
    
    // A. Find last snapshot date *for the current account*
    //    (This part was correct)
    VAR _LastSnapshotDateForAccount =
        CALCULATE (
            MAX ( Fact_GL[Period] ),
            REMOVEFILTERS ( Dim_Date ), 
            Fact_GL[Period] <= _LastDateInContext 
        )
    
    // B. Get the value for that specific date
    //    (This part was correct)
    VAR _BalanceForAccount = 
        CALCULATE (
            SUM ( Fact_GL[USD] ),
            REMOVEFILTERS ( Dim_Date ),
            Fact_GL[Period] = _LastSnapshotDateForAccount
        )
        
    // C. Apply the "no future" rule
    RETURN
    IF (
        _IsDateContextFiltered && _LastDateInContext > _LastOverallSnapshotDate, 
        BLANK(), 
        _BalanceForAccount
    )
)

The resultant output looks like below:

 

Please let me know if this meets your requirement or not.  Usually, if you extract the balance sheet from ERP, even though there is no transaction in particular month, the balances gets generated like above, which carries forward the balance from last non blank months.

I attach a pbix file for your reference.

Best regards,