Her is my approach.
Fist pivot data from edit data source, with selecting all the data fields.
(Header with date )
date(dateparse("MMM-yyyy",[Pivot Field Names]))
str(year([Date Conversion]))+"-Q"+str(datepart('quarter',[Date Conversion]))
Hope this helps.
This is helpful in creating the field names yyyy-q, but it doesn't seem to be summing up the revenue values correctly for each quarter. ie 2017-Q1 should sum the revenue to $183,214. Do you have any idea why this is happening?
Thanks so much Shin! This was exactly what I was looking for.
I'm glad that I could help you.
Hello, I would like to add a similar question:
I want a forecast of revenue per month. I know Apartment A is rented from Jan 15 2018 to April 30 2018. Apartment B is rented from May 20 2018 to April 30 2019. I would like to see the forecast of revenue per month and adjusting a prorated revenue in the first month according with the days used in that month: Apartment A: paying only 15 days of Jan and Apartment B: paying only 20 days of May.
a) how to build the line of time ? Using a blend or left join with a another data column ?
b) how can I adjust the first month of the contract/
I would suggest you to create new question in the Community to get higher chances of getting the solutions.