Column with values from different period

[Anonymous]

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

[v-lili6-msft]

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

[Anonymous]

Hi v-lili6-msft,

Thank you very much! It is like you say, except that Jan18 to Nov18 should be set equal to Dec18 always (so not only if Dec 18=1). Would that change the logic?

[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