Forum Discussion
Helper ICompare Feature Sales with Previous Year Feature Sales
Helper IHi to all and thank you for the thinking process...
I have a Calendar table that is based on the Reservation Date. All the revenue is based on the Reservation Date.
The revenue table has 2 date fields. Reservation and Input dates.
Business wise, looking into the next year asOf today the information is there, as reservations are coming in, I can look into the next year's revenue.
Looking into Last Year i must be able to filter this by Last Year's Input Date asOf 23/12/2015 and see what Revenue I had in 2016.
I can do this by having seperate graphs with seperate filters for Input Year. Graph 1 (Input Year <2017, Reservation Year 2017), Graph 2 (Input Year <2016, Reservation Year 2016).
Thus, I'm asking how I can combine this into one graph?
Thank you for your time.
Resident RockstarShare some sample data , i will help u to achieve your goal
- asl10
Hi Baskar,
Thank you for your offer for help :)
For example you have the following table. You can construct and a calendar table based on the Reservation Date.
Input Date Reservation Date Revenue 01/01/2015 02/02/2016 250 10/05/2015 03/04/2016 100 11/11/2015 10/12/2016 300 27/12/2015 11/11/2016 500 03/04/2016 10/10/2017 100 05/10/2016 05/05/2017 400 20/12/2016 23/12/2017 585 23/12/2016 10/10/2017 100 Thus the goal is to have a graph with 2 bars side-by-side were if you see the data asOf today 23/12/2016 you should see the details by month but total wise will be:
- asOf 23/12/2016 (Input Date) - Result is Reservation dates 2017 with total revenue=1185
- Based on the above asOf date the previous year (thus 23/12/2015) - Result is Reservation Dates 2016 with Revenue = 650
Thank you in advance.
- parry2k
Super User
Hi there,
From your table looks like you just need to calcualte YTD Revenue and here is what you can use for that:
YTD Sales = TOTALYTD(SUM(Sales[Revenue]),'Calendar-1'[Date])
I assumed you already have calendar table and linked to Sales Table on Input Date, make sure relationship is single as shown below.
Results for two different dates
Hope it is helpful, let me know if need further assistance.
- parry2k
This will be your formula for previous year YTD
mYTD Previous Year = TOTALYTD(SUM(Sales[Revenue]),DATEADD('Calendar-1'[Date],-1, YEAR))
Hi asl10,
You can try to use below measures to calculate the previous value.
Current Value(Input Date) = var currDate=MAX(DateTable[Date]) Return SUMX(FILTER(ALL(Sheet1),Sheet1[Input Date]>=DATE(YEAR(currDate),MONTH(currDate),1)&&Sheet1[Input Date]<=currDate),[Revenue]) Current Value(Reservation Date) = var currDate=MAX(DateTable[Date]) Return SUMX(FILTER(ALL(Sheet1),Sheet1[Reservation Date]>=DATE(YEAR(currDate),MONTH(currDate),1)&&Sheet1[Reservation Date]<=currDate),[Revenue]) Previous Value(Input Date) = var currDate=MAX(DateTable[Date]) Return SUMX(FILTER(ALL(Sheet1),Sheet1[Input Date]>=DATE(YEAR(currDate)-1,MONTH(currDate),1)&&Sheet1[Input Date]<=DATE(YEAR(currDate)-1,MONTH(currDate),DAY(currDate))),[Revenue]) Previous Value(Reservation Date) = var currDate=MAX(DateTable[Date]) Return SUMX(FILTER(ALL(Sheet1),Sheet1[Reservation Date]>=DATE(YEAR(currDate)-1,MONTH(currDate),1)&&Sheet1[Reservation Date]<=DATE(YEAR(currDate)-1,MONTH(currDate),DAY(currDate))),[Revenue])
Result:
Regards,
Xiaoxin Sheng- asl10
Hi Anonymous,
Thank you for your input. Your almost there...If you see the below pic my values get messed up regarding the totals. If you add them though the Current Value (reservation Date) is correct. The previous Values measures though bring the totals of the previous year and not the asOf Date amounts as per my initial post pictures when you have the data in 2 seperatetables or graphs.
