Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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 ...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      v-lili6-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      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