Forum Discussion

TK202_BI's avatar
TK202_BI
Frequent Visitor
8 years ago

Need help with create SamePeriodLastYear in Query Editor

In my scenario, I will need to pivot this SamePeriodLastYear column. So I can't do it with DAX. Can anyone help me create it with Power Query M? Thank you in advance.
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi TK202_BI,

 

You can add a column with Date.AddYears function to get SamePeriodLastYear date.

#"Added Custom" = Table.AddColumn(#"Renamed Columns", "SamePeriodLastYear", each Date.AddYears([Date],-1))

 

Regards,

Xiaoxin Sheng

TK202_BI's avatar
TK202_BI
Frequent Visitor
8 years ago

Hi Anonymous,

 

Thanks for your reply. I have my sample file at https://www.dropbox.com/s/um8ivikxnawefai/Sample.pbix?dl=0. The column I have for previous year invoice line amount is created by DAX, which is not able to be pivoted. Can you please take a look and advice how I can achieve this with Power Query? Thanks.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    HI TK202_BI,

     

    >>The column I have for previous year invoice line amount is created by DAX, which is not able to be pivoted. Can you please take a look and advice how I can achieve this with Power Query

    The simplest method is summary your table data by year, then add a custom column to look up the previous year total value. 

     

    Reference:

    Grouping or Summarizing Data

     

    Regards,

    Xiaoxin Sheng

    • TK202_BI's avatar
      TK202_BI
      Frequent Visitor
      8 years ago

      Hi Anonymous, I don't see any option to summary my data by year. I tried both Basic and Advanced. Am I in wrong place?

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi TK202_BI,

         

        You should add a column to store the year part of your data, them use this column as the group column.

         

        Regards,

        Xiaoxin Sheng