MTD,YTD showing total as to date value(December) but not as sum of all MTD,YTD

Hi,

is there any way i can project my MTD, YTD to show total as sum of MTD, sum of YTD instead showing as last month in that year value.
example: for 2018 data, the MTD is showing 2018/dec value as in total, same as for YTD it is showing dec/2018 value.

Have attached my file, we have budget and fact table on monthly basis.

image

the client requirement is to show sum of all MTD,YTD in total for all the facility codes.CMC_Model.pbix (1.8 MB)

Did some quick calculations on one your tables from your model. Is cumulative totals what you are looking for? Here are a couple examples of Tables.
image
image

Thanks
Enterprise%20DNA%20Expert%20-%20Small

Hi ojones,

Thank you for your response. yes am looking for cumulative totals. i should have used more specific word. i apologies for the inconvenience. am bi
if you see my model have created MTD2,YTD2,YTD budget1 are measures that uses switch function

MTD2: 
SWITCH( TRUE(),
	'Facilities'[Selected Value] = "DGRDTN", [MTD Degradation],
	'Facilities'[Selected Value] = "LOSSES", [MTD losses],
	'Facilities'[Selected Value] = "RVLTN", [MTD Reval],
        'Facilities'[Selected Value] = "SWADJSTMT", [MTD S&W]
        )

YTD2 = 
SWITCH( TRUE(),
	'Facilities'[Selected Value] = "DGRDTN", [YTD Degradation1],
	'Facilities'[Selected Value] = "LOSSES", [YTD Losses],
	'Facilities'[Selected Value] = "RVLTN", [YTD Reval],
    'Facilities'[Selected Value] = "SWADJSTMT", [YTD S&W] 
)

YTD2 = 
SWITCH( TRUE(),
	'Facilities'[Selected Value] = "DGRDTN", [YTD Degradation1],
	'Facilities'[Selected Value] = "LOSSES", [YTD Losses],
	'Facilities'[Selected Value] = "RVLTN", [YTD Reval],
    'Facilities'[Selected Value] = "SWADJSTMT", [YTD S&W] 
)
YTD Budget1 = 
SWITCH( TRUE(),
	'Facilities'[Selected Value] = "DGRDTN", [YTD budget(deg)],
	'Facilities'[Selected Value] = "LOSSES", [YTD budget(losses)],
	'Facilities'[Selected Value] = "RVLTN", [YTD budget(reval)],
    'Facilities'[Selected Value] = "SWADJSTMT", [YTD budget(sw)] 
   )

since i believe to work around with cumulative doesn’t work with these measures, is there any other way i can show my cumulative totals for MTD2,YTD2, YTD Budget1?
each MTD’s for all the facilities and for YTD’s for all the facilities are measures but not calculated columns. same as for budget YTD is a measure. am not sure if am using the right approach.
i just started using power BI am new to it.
Thanks.

Here are the measures I came up with. You had a good start to your SWITCH, but here is what it should look like.

Cumulative MTD Measure =
VAR CumulativeDegradation =
    CALCULATE (
        [Total Degradation $],
        FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
VAR CumulativeLosses =
    CALCULATE (
        [Total Losses $],
        FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
VAR CumulativeReval =
    CALCULATE (
        [Total Revaluation $],
        FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
VAR CumulativeSW =
    CALCULATE (
        [Total S&W $],
        FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
RETURN
    SWITCH (
        TRUE (),
        [Selected Value] = "DGRDTN", CumulativeDegradation,
        [Selected Value] = "LOSSES", CumulativeLosses,
        [Selected Value] = "RVLTN", CumulativeReval,
        [Selected Value] = "SWADJSTMT", CumulativeSW,
        CumulativeDegradation + CumulativeLosses + CumulativeReval + CumulativeSW )


Cumulative YTD Budget Measure =
VAR CumulativeBudgetreval =
    CALCULATE (
        [Total Budget(reval)],
        FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
VAR CumulativeBudgetsw =
    CALCULATE (
        [Total Budget(SW)],
        FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
VAR CumulativeBudgetdeg =
    CALCULATE (
        [Total Budget(deg)],
        FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
VAR CumulativeBudgetlosses =
    CALCULATE (
        [Total Budget(losses)],
        FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
RETURN
    SWITCH (
        TRUE (),
        [Selected Value] = "DGRDTN", CumulativeBudgetdeg,
        [Selected Value] = "LOSSES", CumulativeBudgetlosses,
        [Selected Value] = "RVLTN", CumulativeBudgetreval,
        [Selected Value] = "SWADJSTMT", CumulativeBudgetsw,
        CumulativeBudgetdeg + CumulativeBudgetlosses + CumulativeBudgetreval + CumulativeBudgetsw
    )

Thanks
Enterprise%20DNA%20Expert%20-%20Small

1 Like

Thanks again, after using these measures, still my total doesn’t show the right numbers.

image

Send me over a copy of the PBIX file so I can compare. You also need to adjust your [Selected Measure] to look like this:
Selected Value = SELECTEDVALUE( Facilities[Facilities] )
** The reason your total is -70,110,081.31 is due to your measure referring to “DGRDTN”.

Here is what it looks like on my copy:

image

Thanks
Enterprise%20DNA%20Expert%20-%20Small

oh man you are my savior. Thanks much!! it worked.:grinning:

1 Like