Forum Discussion
Need help with calculated column for MTD value
Hi, I have a data table with the YTD General Ledger account value, by period (2301 means 2023 Jan), and would like to create columns for the MTD and QTD values in Power Query. What should the cal...
I did the below calculated column but it didn't turn out correctly. It seemed to have added the values for all Acc No for each Acc No. Appreciate if someone can help to advise.
MTD Acc Value (RMB) = VAR vDate = NPI[Period] VAR vPrevDate = CALCULATE( MAX(NPI[Period]), ALL(NPI), NPI[Period] < vDate ) VAR vPrevAccValueRMB = CALCULATE( SUM(NPI[YTD Acc Value (RMB)]), ALL(NPI), NPI[Period] = vPrevDate ) VAR vYTD = NPI[YTD Acc Value (RMB)] VAR vMTD = vYTD - vPrevAccValueRMB VAR vResult = IF(VALUE(RIGHT(vDate,2)) = 1, vYTD, vMTD) RETURN vResult |
Please see the current results based on the above calculated column
Acc No Sum of MTD Acc Value (RMB) Actuality Period 123233 1 AC 2301 123233 -2 AC 2302 123233 -5 AC 2303 123233 -8 AC 2304 123233 -11 AC 2305 123233 -14 AC 2306 123233 1 BU 2301 123233 -2 BU 2302 123233 -5 BU 2303 123233 -8 BU 2304 123233 -11 BU 2305 123233 -14 BU 2306 123233A 1 AC 2301 123233A -2 AC 2302 123233A -5 AC 2303 123233A -8 AC 2304 123233A -11 AC 2305 123233A -14 AC 2306 123233A 1 BU 2301 123233A -2 BU 2302 123233A -5 BU 2303 123233A -8 BU 2304 123233A -11 BU 2305 123233A -14 BU 2306