Forum Discussion
Column with values from different period
Hello everyone,
I'm looking for a column that equals the headcount column below except for 2018, where I want to "overwrite" the headcount with December 18 values. So if Dec18 = 1 than all other months in 18 should be 1 too. Anybody knows an easy way to do this?
I tried to use dateadd with instead of "-1" using "+(12 - Month)" but that returns only blanks.
Thanks
NameYearMonth numberMonthHeadcount
| Person1 | 2018 | 1 | January | 1 |
| Person1 | 2018 | 2 | February | 1 |
| Person1 | 2018 | 3 | March | 1 |
| Person1 | 2018 | 4 | April | 1 |
| Person1 | 2018 | 5 | May | 1 |
| Person1 | 2018 | 6 | June | 1 |
| Person1 | 2018 | 7 | July | 1 |
| Person1 | 2018 | 8 | August | 1 |
| Person1 | 2018 | 9 | September | 1 |
| Person1 | 2018 | 10 | October | 1 |
| Person1 | 2018 | 11 | November | 1 |
| Person1 | 2018 | 12 | December | 1 |
| Person1 | 2019 | 1 | January | 1 |
| Person1 | 2019 | 2 | February | 1 |
| Person1 | 2019 | 3 | March | 1 |
| Person1 | 2019 | 4 | April | 1 |
| Person1 | 2019 | 5 | May | 1 |
| Person1 | 2019 | 6 | June | 0 |
| Person1 | 2019 | 7 | July | 0 |
| Person1 | 2019 | 8 | August | 0 |
| Person1 | 2019 | 9 | September | 0 |
| Person1 | 2019 | 10 | October | 0 |
| Person1 | 2019 | 11 | November | 0 |
| Person1 | 2019 | 12 | December | 0 |
| Person2 | 2018 | 1 | January | 1 |
| Person2 | 2018 | 2 | February | 1 |
| Person2 | 2018 | 3 | March | 1 |
| Person2 | 2018 | 4 | April | 1 |
| Person2 | 2018 | 5 | May | 1 |
| Person2 | 2018 | 6 | June | 1 |
| Person2 | 2018 | 7 | July | 1 |
| Person2 | 2018 | 8 | August | 1 |
| Person2 | 2018 | 9 | September | 1 |
| Person2 | 2018 | 10 | October | 1 |
| Person2 | 2018 | 11 | November | 0 |
| Person2 | 2018 | 12 | December | 0 |
| Person2 | 2019 | 1 | January | 0 |
| Person2 | 2019 | 2 | February | 0 |
| Person2 | 2019 | 3 | March | 0 |
| Person2 | 2019 | 4 | April | 0 |
| Person2 | 2019 | 5 | May | 0 |
| Person2 | 2019 | 6 | June | 0 |
| Person2 | 2019 | 7 | July | 0 |
| Person2 | 2019 | 8 | August | 0 |
| Person2 | 2019 | 9 | September | 0 |
| Person2 | 2019 | 10 | October | 0 |
| Person2 | 2019 | 11 | November | 0 |
| Person2 | 2019 | 12 | December | 0 |
3 Replies
v-lili6-msftCommunity Support
HI, Anonymous
I'm a little confused about your description, do you mean that just for 2018, if headcount on December 18 =1 then all other months in 18 should be 1 too, otherwise the headcount should stay the same?
If so, try this formual:
Column = VAR Dec18 = IF ( Table1[Year] = 2018, CALCULATE ( SUM ( Table1[Headcount] ), FILTER ( Table1, Table1[Name] = EARLIER ( Table1[Name] ) && Table1[Year] = 2018 && Table1[Month] = 12 ) ) ) RETURN IF ( Dec18 = 1, 1, Table1[Headcount] )If not your case, please share the expected output of the sample data below.
Best Regards,
Lin
- v-lili6-msft
hi, Anonymous
You could try it by yourself, the logic should be the same.
If it is not your case, please share pbix file or some data sample and expected output. Do mask sensitive data before uploading.
Best Regards,
Lin