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 |