Unable to get the YTD number for a given total

[kapilg89]

I'm working with a trial balance which gives output in YTD format i.e. it rolls up the numbers from Jan to whichever month you are downloading the data for. Now I want to work our the amounts for the month for which I have written the below dax

Below is my Base data named "OS TB_Input" which is granular to the GL and Cost Centre and I have a Dimention table for both GL and Cost centre separately and mapped to this Fact table along with a Calendar table mapped to the Month Column. Amount is the column with YTD numbers for each particular month for that GL/ Cost Centre.

Base Table Input Data

"[Trial Balance]" just sums up the amount column which is YTD numbers.

Trial Balance = SUM('OS TB_Input'[Amount])

Monthly is a measure created to find the monthly values

Monthly = 
VAR PrevMonth = 
    CALCULATE(
        [Trial Balance],
        PREVIOUSMONTH('Calendar'[Date]),
        COA_Grouping[P&L/ BS] = "P&L"
    )
VAR CurrentMonth = 
    CALCULATE(
        [Trial Balance],
        COA_Grouping[P&L/ BS] = "P&L"
    )

RETURN
IF(
    ISBLANK(CurrentMonth),
    BLANK(),
    [Trial Balance]- PrevMonth
)

Although this gives the monthly numbers correctly but it messes up the totals as what I expect in the total yearly column is the sum of all the Months whereas it's showing up a much different number.

Output with monthly split

Pl help to correct the totals and also if someone could suggest how to improve the dax, that would be of great help.

[v-sgandrathi · Accepted solution]

Hi @kapilg89,

The issue arises because the DAX measure that calculates monthly values by subtracting the previous month's YTD amount from the current month's YTD amount works correctly at the individual month level but fails at the total level. In a matrix visual, each row provides the necessary context for PREVIOUSMONTH to return the correct prior month, allowing the formula to calculate accurate month-over-month differences. However, in the total row, there is no specific month context, so the PREVIOUSMONTH function returns blank. As a result, the measure ends up subtracting blank from the current YTD value, effectively displaying the full YTD total again instead of summing the monthly values. This leads to an inflated or misleading total that doesn't represent the actual sum of the monthly changes across the year.

To resolve the issue you're facing with incorrect totals in your monthly measure, I recommend using a combination of SUMMARIZE  and SUMX in your DAX formula. The key idea is to avoid relying on the visual’s row context, which works fine for individual months but fails at the total level due to how PREVIOUSMONTH behaves. Instead, use SUMMARIZE to create a virtual table that explicitly lists each distinct month. For each of these months, calculate the monthly value as the difference between the current month's YTD and the previous month's YTD. Then, use SUMX to iterate over this table and sum the individual monthly values. This approach ensures that your measure returns accurate monthly figures in both detailed and total views, preventing the total from incorrectly showing the final YTD value instead of the sum of monthly changes.

I hope this helped! Feel free to ask any further questions. If this resolved your issue, please mark it as "Accept as Solution" and give us Kudos to assist others.

Thank you.

[kapilg89]

I've used the below function to work out a summarized table. In a nutshell, this code creates 2 separate tables, one for P&L where I need the amounts monthly and one for Balance sheet where I just need the number as is and then using "Union()", I've combined the two tables together. This is working for me now thanks.

Summarized TB = 
VAR PL =
SUMMARIZECOLUMNS(
    COA_Grouping[P&L/ BS]
    , COA_Grouping[GL]
    , COA_Grouping[GL Description]
    , 'Cost Centre'[Cost Centre]
    , 'Calendar'[Date]
    , FILTER(
        COA_Grouping
        , COA_Grouping[P&L/ BS] = "P&L"
    )
    , "Ending Balance", SUM('OS TB_Input'[Amount])
    , "PrevMonth", CALCULATE(
        SUM('OS TB_Input'[Amount])
        , PREVIOUSMONTH('Calendar'[Date])
    )
)

VAR BS =
SUMMARIZECOLUMNS(
    COA_Grouping[P&L/ BS]
    , COA_Grouping[GL]
    , COA_Grouping[GL Description]
    , 'Cost Centre'[Cost Centre]
    , 'Calendar'[Date]
    , FILTER(
        COA_Grouping
        , COA_Grouping[P&L/ BS] = "BS"
    )
    , "Ending Balance", SUM('OS TB_Input'[Amount])
    , "PrevMonth", SUM('OS TB_Input'[Amount])
)

VAR Monthly_PL= 
    ADDCOLUMNS(
        FILTER(
        PL
        , NOT ISBLANK([Ending Balance])
    )
    , "Monthly", [Ending Balance]- [PrevMonth]
)

VAR BS_Final= 
    ADDCOLUMNS(
        FILTER(
        BS
        , NOT ISBLANK([Ending Balance])
    )
    , "Monthly", [Ending Balance]
)
VAR TB_Final = UNION(Monthly_PL, BS_Final)

RETURN TB_Final

[DataNinja777]

Hi @kapilg89 ,

If you’ve got access to the GL or TB data at a monthly level (especially from ERPs like SAP or Oracle), you wouldn't need to even bother with this whole YTD-back-to-month wrangling. Just get the monthly values directly from ERP and simplify your life. Transaction lists from ERP are also excellent source data if you're trying to be precise — they let you rebuild everything bottom-up including TB with more granularity.

But in the absence of monthly granularity, the SUMX-based DAX trick below is your best bet. 

IF(ISBLANK(...)) is notorious for messing up totals because BLANK() in row context doesn’t mean the same thing in total context. 

Here’s a better version that ditches IF and leans on math to behave properly in totals:

Monthly = 
SUMX(
    VALUES('Calendar'[Date]),
    VAR Current =
        CALCULATE(
            [Trial Balance],
            COA_Grouping[P&L/ BS] = "P&L"
        )
    VAR Previous =
        CALCULATE(
            [Trial Balance],
            COA_Grouping[P&L/ BS] = "P&L",
            DATEADD('Calendar'[Date], -1, MONTH)
        )
    RETURN
        Current - COALESCE(Previous, 0)
)

By using COALESCE, you're making sure that even if the previous value is blank, it doesn’t mess up your logic — it just assumes a 0 instead. No IF, means no total messing up. Just the deltas you expect, with totals that actually add up.

Best regards,