Search code examples
powerbidaxm

Split single row of data in Power BI data source into multiple rows

I have a table in a Power BI data source with a column for term start and term end date (term length can be longer than a month), along with meta data on the term. I need to report on status of purchased terms as at the end of each month. As far as I can see, the best way of accomplishing this would be to create a calculated table with an entry for each month on which a term is active at its end.

For example, an entry in the original table with the following data:

TermStartDate TermEndDate PurchaseAmount
2018-01-03    2018-04-12  100

Would end up in the calculated table as follows:

MonthPurchased PurchaseAmount
2018-01        100
2018-02        100
2018-03        100

How to accomplish this? Is there a better way than creating a separate calculated table to get this data? Any help or advise is appreciated

Solution

  • I managed to solve this myself, I detail the required steps below for reference:

    1. Change start and end date column data types from Datetime to Date. <- This is needed to ensure we only generate dates on day boundaries in the next step

    2. Add custom column with the following formula:

      Month = List.Select( List.Dates([TermStartDate], Number.From([TermEndDate] - [TermStartDate]) +1, #duration(1, 0, 0, 0)), each _ = Date.EndOfMonth(_) )

      This generates a list of dates between start and end, then filters to only leave the dates that are at the end of a month

    3. Expand to new rows on the new Month column (menu at the top of the column)

    4. Use Detect Data Type option on the Month column to change the datatype from Any to Date (for some reason I cannot manually select Date, the DataType menu option is greyed out on the Month column)