Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Setting year as column

I have a matrix where I have the code as a row and the year as a column. Each column brings the sum of a value for the year it exists.
example: I have the code 123456 which has data for the years: 2021,2022,2023,2025,2029. So the matrix brings the value only for each year that the value exists.

I would like to bring the years 2026,2027 and 2028, bringing an empty value, that is, I would like to bring the years within the period, even if they have no value and there is also no record for them in the bank. I want to bring the years

 

how is it today

 

how I want it to be 

 

Need Help

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      But i do not have a row with the year with no data. 

       

      I only have a row for years 21,22,23,25 and 29.

      There is no row for the years 24, 26, 27 and 28.

       

      I tried  the way you said, but it not work

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous 

     Have you solved this problem yet? If not, you may take steps below for reference.

    1. Transpose the table

    1. then, create a year table:

     

    year table =
VAR _start =
    MIN ( 'Table'[CODE] )
VAR _end =
    MAX ( 'Table'[CODE] )
RETURN
    GENERATESERIES ( _start, _end )

     

    3.create the relationship between 2 tables:

    Result:

     

    Best Regards,

    Community Support Team _ Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.