Split per month recurrent values

[MigCua]

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!

[BA_Pete · Accepted solution]

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]

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]

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

[MigCua]

Hi @BA_Pete , I have review again the script and the issue was I had writen the column name without capital leters, now it is working perfectly!

Thank you very much!