Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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. (From this calculated table I can then perform the aggregations as needed)
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!
Solved! Go to Solution.
Hi @Anonymous
You could create this table with Power Query
Open Edit Queries
Create a copy of your data table, in this copied table
1.Add a custom column(Add column->custom column)
=List.Dates([termstartdate],Duration.TotalDays([termenddate]-[termstartdate])+1,#duration(1,0,0,0))
Then click on the arrow on the field to "Expand to new rows"
2. select the [listdate] column, then select "Add column"->"Date"->Month->End of Month
You will get a column to show the end of month for each month of [listdate] column
3.create a conditional column(Add column->Add conditional column)
4. then click on the "down" arrow on the [filtercondition] column, select "Remove empty", you will get such table
Finally, you could remove columns from this table except the [purchase_id], [purchase_amount], [filtercondition] columns
Please refer to my pbix
Best reagrds
Maggie
Hi @Anonymous
You could create this table with Power Query
Open Edit Queries
Create a copy of your data table, in this copied table
1.Add a custom column(Add column->custom column)
=List.Dates([termstartdate],Duration.TotalDays([termenddate]-[termstartdate])+1,#duration(1,0,0,0))
Then click on the arrow on the field to "Expand to new rows"
2. select the [listdate] column, then select "Add column"->"Date"->Month->End of Month
You will get a column to show the end of month for each month of [listdate] column
3.create a conditional column(Add column->Add conditional column)
4. then click on the "down" arrow on the [filtercondition] column, select "Remove empty", you will get such table
Finally, you could remove columns from this table except the [purchase_id], [purchase_amount], [filtercondition] columns
Please refer to my pbix
Best reagrds
Maggie
Thanks Maggie.
I got his working basically 10 minutes ago 🙂 My approach was very similar to your proposed answer, for reference I detail the steps below:
Month = List.Select( List.Dates([TermStartDate], Number.From([TermEndDate] - [TermStartDate]) +1, #duration(1, 0, 0, 0)), each _ = Date.EndOfMonth(_) )
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |