topic Re: MTD calculated column in DAX Commands and Tips

MTD calculated column

Anonymous — Fri, 17 Dec 2021 14:56:37 GMT

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)

Re: MTD calculated column

amitchandak — Fri, 17 Dec 2021 17:09:14 GMT

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

Re: MTD calculated column

v-xiaotang — Tue, 21 Dec 2021 06:26:38 GMT

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.