Log In
Register
Home
Active Member
November 8, 2019
Offline
Relative newbie here,
So I have many thousands of rows of data which are group on several parameters multiple product, multiple grades but for the sake of ease here I've included 1 product which has 2 grades.
I'm grouping by Product and Grade
On a time scale you would have actuals up to current date (Actuals) and then on from that you have the additions (joiners) in the future months
I'm trying to replicate the Total line which:
takes the latest actual position (in this case period 202103) and adds the amount of joiners in the following months. These aren't cumulative joiners in the months going forward, I'm simply trying to add 2 numbers together
In power query this is currently in an unpivoted form around the date, so only has 5 columns but switched around here for readability
Tried creating this in power query and power pivot explicit measures (neither successfully)
Question :- can the total line be created or would it be better as an explicit measure? if so how would i achieve either please.
| Product | Grade | 202101 | 202102 | 202103 | 202104 | 202105 | 202106 | |
| Joiners | 695421 | E | 1 | 1 | 1 | |||
| Joiners | 695421 | F | 2 | 3 | 4 | |||
| Actual | 695421 | E | 12 | 12 | 13 | |||
| Actual | 695421 | F | 31 | 25 | 23 | |||
| Total | 695421 | E | 12 | 12 | 13 | 14 | 14 | 14 |
| Total | 695421 | F | 31 | 25 | 23 | 25 | 26 | 27 |
sorry I cant supply original workbook
thanks
See Answer
Trusted Members
Moderators
November 1, 2018
Offline
Roughly speaking, in PQ you could get the latest date for each Actual value for a given Product/Grade combination, and using that get the latest value. Then all you need to do is add that value on if it's Joiners data, or just use the current value if it's Actual.
Answers Post
1 Guest(s)
