Unable to get the YTD number for a given total
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. "[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. Pl help to correct the totals and also if someone could suggest how to improve the dax, that would be of great help.
