Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Basis-value from a basis year - repeating using month only

I have a table with 4 columns, namely: Year, Month, Name and value.

Year containts 2018 to 2020 with months 1 - 12 for each Name: A - C.

What is important for me is to make the 2018 values as base comparing year. So For names A - C and months of 1 - 12 in 2018, I would like a new column to repeat the values of 2018, holding the month as filter in the coming years, se below example (Repeatme column is what i want it to look like), and the following is in excel, im using it as example to show:

 

I will upload the following excel, so you may use it as datasource. 

I have now worked on this simple task for many hours..hopefully some genius knows a thing or two. 
Link for the data 

 

 

 

General Comment
Need Help
Tips and Tricks
Tutorial Requests

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , New column =

    Sumx(filter(Table, [Name] =earlier([Name])  && [Month] =earlier([Month])  && [Year] =2018) , [value])

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      amitchandak Thank you for your reply. I am getting repeating values based on month and year as i wish..however the displayed value is wrong, since i believe it stems from the Sumx, So i am getting a summed version of the real value, which i want.  1 is the one i wanna repeat on the empty spaces based on month and the 2 is what i get with the sumx you mentioned above. 
      is there any workaround?

       

      • v-kkf-msft's avatar
        v-kkf-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous ,

         

        In your example data, amitchandak 's formula is able to return the correct result. So in your actual data, are you grouping by additional fields (i.e. fields other than Name, month, year)?

         

        Also you can try to fill down using Power Query.
        1. Add custom column.

         

        2. Sort the table. Sort by month, Name, year in that order.

        = Table.Sort(#"Added Custom",{{"month", Order.Ascending},{"Name", Order.Ascending},{"year", Order.Ascending}})

         

        3. Select the custom column and fill down.

         

         

        The complete code is as follows.

         

        let
    Source = Excel.Workbook(File.Contents("\\xxxxxx\Data-example.xlsx"), null, true),
    Ark1_Sheet = Source{[Item="Ark1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Ark1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"year", Int64.Type}, {"month", Int64.Type}, {"Name", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "FillDown", each if [year] = 2018 then [Value] else null),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"month", Order.Ascending},{"Name", Order.Ascending},{"year", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"FillDown"})
in
    #"Filled Down"

         

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
        Best Regards,
        Winniz
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.