MTD calculated column

[Anonymous]

Hi,

I have source data amounts presented in year-to-date (IBM Cognos) and are trying to create a calculated column in the fact table that is presenting the month-to-date value. Example below:

Although I managed to calulate this using below DAX formula have I not found a way of calculating a month-to-date value when no year-to-date value has been reported in one period. Example in the data set above are revenues coded on cost center (DIM3) "4112" in the beginning of the year although changed to "4113" in June. The correct MTD value for June should have been -210 (=0-210) on DIM3 "4112" and +290 (=290-0) on DIM3 "4113" (i.e. together 40). However since Im missing a row with zero YTD amount for DIM4112 in June are my formula not calculating that row and end up showing +290 in total for the month.

Any guidance on how to solve this challenge is highly appreciated!

Thanks

Alexander

AmountMTD =
VAR _Dim1 = 'DW V_FactControllerLedgerExpanded'[DIM1]
VAR _Dim2 = 'DW V_FactControllerLedgerExpanded'[DIM2]
VAR _Dim3 = 'DW V_FactControllerLedgerExpanded'[DIM3]
VAR _Acc = 'DW V_FactControllerLedgerExpanded'[Account]

VAR _PrevPeriod = DATEADD('DW V_FactControllerLedgerExpanded'[Date], -1, MONTH)

VAR _PrevValue = LOOKUPVALUE('DW V_FactControllerLedgerExpanded'[AmountYTD],
'DW V_FactControllerLedgerExpanded'[DIM1], _Dim1,
'DW V_FactControllerLedgerExpanded'[DIM2], _Dim2,
'DW V_FactControllerLedgerExpanded'[DIM3], _Dim3,
'DW V_FactControllerLedgerExpanded'[Account], _Acc,
'DW V_FactControllerLedgerExpanded'[Date], _PrevPeriod)

VAR _MTDvalue = 'DW V_FactControllerLedgerExpanded'[AmountYTD] - _PrevValue

RETURN
IF(MONTH('DW V_FactControllerLedgerExpanded'[Date]) = 1, 'DW V_FactControllerLedgerExpanded'[AmountYTD], _MTDvalue)

[v-xiaotang]

Hi @Anonymous 

You could create create an independent MTD column, please provide more details, then we can readjust your columns according to your needs.

(1) a sample file, you can replace raw data with bogus data to protect your privacy.

(2) or provide some sample data that fully covers your issue/question

(3) give your expected result based on the sample you provide

(Kindly note: Please ensure the data in sample is concise and representative)

Thanks.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , A new column

New column =
[YTD] = maxx(filter(Table, [Date] = earlier([Date])-1 && [Account] = earlier([Account] )
&& [DIM1] = earlier([DIM1] )
&& [DIM2] = earlier([DIM2] ) ),[YTD])

Add and remove equal joins as per need