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 calculated column formula be like?
Appreciate any help.
Please see below sample of the data:
| Project | Actuality | Period | Currency | NPI Company | Product Type | Acc No | YTD Acc Value (RMB) |
| Project 1 | AC | 2301 | RMB | 1213 | 123233 | 1 | |
| Project 1 | AC | 2301 | RMB | 1213 | 123233A | 1 | |
| Project 1 | AC | 2302 | RMB | 1213 | 123233 | 2 | |
| Project 1 | AC | 2302 | RMB | 1213 | 123233A | 2 | |
| Project 1 | AC | 2303 | RMB | 1213 | 123233 | 3 | |
| Project 1 | AC | 2303 | RMB | 1213 | 123233A | 3 | |
| Project 1 | AC | 2304 | RMB | 1213 | 123233 | 4 | |
| Project 1 | AC | 2304 | RMB | 1213 | 123233A | 4 | |
| Project 1 | AC | 2305 | RMB | 1213 | 123233 | 5 | |
| Project 1 | AC | 2305 | RMB | 1213 | 123233A | 5 | |
| Project 1 | AC | 2306 | RMB | 1213 | 123233 | 6 | |
| Project 1 | AC | 2306 | RMB | 1213 | 123233A | 6 | |
| Project 1 | BU | 2301 | RMB | 1213 | 123233 | 1 | |
| Project 1 | BU | 2301 | RMB | 1213 | 123233A | 1 | |
| Project 1 | BU | 2302 | RMB | 1213 | 123233 | 2 | |
| Project 1 | BU | 2302 | RMB | 1213 | 123233A | 2 | |
| Project 1 | BU | 2303 | RMB | 1213 | 123233 | 3 | |
| Project 1 | BU | 2303 | RMB | 1213 | 123233A | 3 | |
| Project 1 | BU | 2304 | RMB | 1213 | 123233 | 4 | |
| Project 1 | BU | 2304 | RMB | 1213 | 123233A | 4 | |
| Project 1 | BU | 2305 | RMB | 1213 | 123233 | 5 | |
| Project 1 | BU | 2305 | RMB | 1213 | 123233A | 5 | |
| Project 1 | BU | 2306 | RMB | 1213 | 123233 | 6 | |
| Project 1 | BU | 2306 | RMB | 1213 | 123233A | 6 |
Please see the required end result:
| Project | Actuality | Period | Currency | NPI Company | Product Type | Acc No | YTD Acc Value (RMB) | MTD Acc Value (RMB) | QTD Acc Value (RMB) |
| Project 1 | AC | 2301 | RMB | 1213 | 123233 | 1 | 1 | 1 | |
| Project 1 | AC | 2301 | RMB | 1213 | 123233A | 1 | 1 | 1 | |
| Project 1 | AC | 2302 | RMB | 1213 | 123233 | 2 | 1 | 2 | |
| Project 1 | AC | 2302 | RMB | 1213 | 123233A | 2 | 1 | 2 | |
| Project 1 | AC | 2303 | RMB | 1213 | 123233 | 3 | 2 | 3 | |
| Project 1 | AC | 2303 | RMB | 1213 | 123233A | 3 | 2 | 3 | |
| Project 1 | AC | 2304 | RMB | 1213 | 123233 | 4 | 2 | 1 | |
| Project 1 | AC | 2304 | RMB | 1213 | 123233A | 4 | 2 | 1 | |
| Project 1 | AC | 2305 | RMB | 1213 | 123233 | 5 | 3 | 2 | |
| Project 1 | AC | 2305 | RMB | 1213 | 123233A | 5 | 3 | 2 | |
| Project 1 | AC | 2306 | RMB | 1213 | 123233 | 6 | 3 | 3 | |
| Project 1 | AC | 2306 | RMB | 1213 | 123233A | 6 | 3 | 3 | |
| Project 1 | BU | 2301 | RMB | 1213 | 123233 | 1 | 1 | 1 | |
| Project 1 | BU | 2301 | RMB | 1213 | 123233A | 1 | 1 | 1 | |
| Project 1 | BU | 2302 | RMB | 1213 | 123233 | 2 | 1 | 2 | |
| Project 1 | BU | 2302 | RMB | 1213 | 123233A | 2 | 1 | 2 | |
| Project 1 | BU | 2303 | RMB | 1213 | 123233 | 3 | 2 | 3 | |
| Project 1 | BU | 2303 | RMB | 1213 | 123233A | 3 | 2 | 3 | |
| Project 1 | BU | 2304 | RMB | 1213 | 123233 | 4 | 2 | 1 | |
| Project 1 | BU | 2304 | RMB | 1213 | 123233A | 4 | 2 | 1 | |
| Project 1 | BU | 2305 | RMB | 1213 | 123233 | 5 | 3 | 2 | |
| Project 1 | BU | 2305 | RMB | 1213 | 123233A | 5 | 3 | 2 | |
| Project 1 | BU | 2306 | RMB | 1213 | 123233 | 6 | 3 | 3 | |
| Project 1 | BU | 2306 | RMB | 1213 | 123233A | 6 | 3 | 3 |
4 Replies
- foodd
Community Champion
Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied. 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 - vPrevAccValueRMBVAR vResult =IF(VALUE(RIGHT(vDate,2)) = 1, vYTD, vMTD)RETURNvResultPlease 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
Appreciate any hints or comments.
