Forum Discussion

MigCua's avatar
MigCua
Frequent Visitor
4 years ago
Solved

Split per month recurrent values

Hi!

I have a doubt of how to build a report.

I have a table where I can find diferent lines related to item which has the following structure:

- Item code

- Date valid From

-Date Valid to

- Monthly Revenue

 

I would like to see how this is distributed per months. For example, an item which has a valid date from 01/01/2022 to 01/04/2022 and a monthly revenue of 20€, and other which is from 01/02/2022 to 01/07/2022 of 30€, I would like to agregate this data by month:

- 2022-01=20€, 2022-02=50€, 2022-03=50€, 2022-04=50€,2022-05=30€...

How could I do so?

Thanks in advance!

 

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi MigCua ,

     

    Add a new custom column in Power Query like this:

    List.Distinct(
    List.Transform(
        {Number.From([Date valid from])..Number.From([Date valid to])},
        each Date.StartOfMonth(Date.From(_))
    )
)

     

    This should give you a nested list of the first of each month on each row.

    Expand this list to new rows and you'll have everything you nee to report as required.

     

    Pete

4 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi MigCua ,

     

    Add a new custom column in Power Query like this:

    List.Distinct(
    List.Transform(
        {Number.From([Date valid from])..Number.From([Date valid to])},
        each Date.StartOfMonth(Date.From(_))
    )
)

     

    This should give you a nested list of the first of each month on each row.

    Expand this list to new rows and you'll have everything you nee to report as required.

     

    Pete

    • MigCua's avatar
      MigCua
      Frequent Visitor
      4 years ago

      Hi Pete, thanks for your fast response!
      This column should be created within the same table as the data is located right?
      I have tried to used the script you are stating but I am getting error, is there anything i need to change from the end of the clause?

      Thanks again!!

      • BA_Pete's avatar
        BA_Pete
        Super User
        4 years ago

        Hi MigCua ,

         

        Difficult to say what needs to be done without more info.

        What error do you get when you try and use this code?

         

        If you can share some anonymised sample data in a copyable format here (paste table from Excel, or provide M code from an 'Enter Data' query) I can create the steps for you.

         

        Pete