Compare Feature Sales with Previous Year Feature Sales

[asl10]

Hi,

I would like to compare sales of reserved bookings (Hotel) for 2017 with reserved booking sales for the same period last year?

E.g. Based on Input Date of users 2016 i can see the figures for 2017. But based on input date of 2015 based on current date now i cannot see what was the result, in one graph side-by-side! I only managed to do it with two graphs and using 2 types of Year filters on each graph for 2016 and 2015. (Reserved Date and Input Date).

Can i make a DAX formula or something in order to have the 2 values side-by-side in one graph?

[austinsense]

A little trouble understanding what you're trying to do - here's the basics of calculating This Year vs. Last Year

Bookings (ThisYear) = SUM(Table[Bookings])

Bookings (LastYear) = CALCULATE( [Bookings (ThisYear)], DATEADD(Calendar[Date], -12, MONTH))

Let me know if this helps.

[asl10]

Let me explain the business case:

For example asOf today, 23/12/16 (Input Date) I have xxxxx Total Revenue for 2017 based on Reservation Date.

For Last Year, asOf  23/12/15 (Input Date) what I had in Total Revenue for 2016 based on Reservation Date?

[austinsense]

A few more questions ...

1. Are the input date and reservation dates two separate fields in the model?

2. Looking into next year, when you choose 23/12/16 which reservation dates in the next year do you expect to see?

3. Same question.  Looking into last year, when you choose 23/12/16 which reservation dates in the last year do you expect to see?

[asl10]

Hi 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.

[Baskar]

Share 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.

[v-shex-msft]

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 @v-shex-msft,

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.

[v-shex-msft]

Hi @asl10,

According to your screenshot, I modified my formula.(current, it calculate the value based on year and month)

Current Value(Input Date) = 
var currDate=MAX(DateTable[Date])
Return
SUMX(FILTER(ALL(Sheet1),Sheet1[Input Date].[Year]=YEAR(currDate)&&Sheet1[Input Date].[MonthNo]=MONTH(currDate)),[Revenue]) 

Current Value(Reservation Date) = 
var currDate=MAX(DateTable[Date])
Return
SUMX(FILTER(ALL(Sheet1),Sheet1[Reservation Date].[Year]=YEAR(currDate)&&Sheet1[Reservation Date].[MonthNo]=MONTH(currDate)),[Revenue]) 

Previous Value(Input Date) = 
var currDate=MAX(DateTable[Date])
Return
SUMX(FILTER(ALL(Sheet1),Sheet1[Input Date].[Year]=YEAR(currDate)-1&&Sheet1[Input Date].[MonthNo]=MONTH(currDate)),[Revenue]) 

Previous Value(Reservation Date) = 
var currDate=MAX(DateTable[Date])
Return
SUMX(FILTER(ALL(Sheet1),Sheet1[Reservation Date].[Year]=YEAR(currDate)-1&&Sheet1[Reservation Date].[MonthNo]=MONTH(currDate)),[Revenue]) 

Regards,

Xiaoxin Sheng

[asl10]

Hi @v-shex-msft, 

Its wrong again. Below you will find a picture of a sample that i have done based on my data and show where i want to go:

The correct result is the 2 tables side by side.

How can i merge these 2 tables with a formula so as to not get the wrong result shown in the graph?

[CheenuSing]

Hi @asl10

I forgot to mention that the SlicerTable should not be linked to anyother table it should be a stand alone one.

Cheers

CheenuSing

[asl10]

Hi @CheenuSing,

Thank you for your input. Very innovative solution and almost there as with my real data the past value was showing all the total and not the specific total between the specific dates. Furthermore, this solution is limited for comparissons and decision making as i do not have the months and cannot filter accordingly to any other data. Currently i have this solution (see below pics) which the years are manually entered as a filter in the 2 graphs. I'm after the same thing but in the same graph, like when we have YTD and Last Year YTD side by side.

I after somethin like this:

[CheenuSing]

Hi @asl10

Please provide sample of actual data and the final output you desire. I am sure we can find the solution.

The data you can share using one drive.

Cheers

CheenuSing

[asl10]

Dear @CheenuSing,

The actual data are the same principal as my sample data. Its the same with just more dates and revenue.

The final output im trying to achieve is like the last picture. To have the revenue side-by-side on a single graph based on the input date asOf today and input date asOf today Last Year.

[CheenuSing]

Hi @asl10

I think I have found a solution for your problem.

The solution is creating a slicer table of Input Dates and do the computation and display.

The steps I did.

1. Created a calendar table as 

   Calendar = Calendar(Min(Reservations[Reservation Date]),max(Reservations[Reservation Date]))

2. Created a year column in this table as Year = YEAR('Calendar'[Date])

3. Created relationship between Calendar Date and Reservations Date.

4. Created a Summarized Table as

      SlicerTable = Summarize(Reservations,Reservations[Input Date])

5. Renamed the Input Date as SlicerInput

6. Created a slicer using SlicerInput.

7. Created a measure SumRevenue = sum([Revenue])

8. Created a measure 

            FutureValue(Input Date) =
                    var FutureYear = Year(Values(SlicerTable[SliceInput]) )+1

                     Return
                    CALCULATE([SumRevenue],FILTER(Calendar,Calendar[Year]=FutureYear))

9. Created a measure

                 PastDate = Date( Year (Values(SlicerTable[SliceInput])) - 1, Month(Values(SlicerTable[SliceInput]))  

                                        ,Day(Values(SlicerTable[SliceInput]))) 

     somehow Dateadd was not giving me the results so had to use the base coding

10. Created a measure 

                PastValue(Input Date) =

                                                      CALCULATE([SumRevenue],

                                                                               Filter (Reservations,[Input Date] <=[PastDate] )
                                                                          )

11. That is it.

12. Created a bar graph

       

It gives the result expected by you.

Couple of doubts I still have is will Reservation Date always point one year ahead of the Input Date ? If not then I need to burn some more calories to derive a wholesome solution.

If this works for you please accept it as a solution and also give KUDOS.  I am requesting other members also for the same.

Cheers

CheenuSing

[parry2k]

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.

[asl10]

Hi , thank you for the input.
If you see my initial post there is a picture where im able to do that with 2 seperate graphs as well.

My question was how to combine them into one! Maybe wth the help of some DAX formula or some other magic trick...

[parry2k]

This will be your formula for previous year YTD

mYTD Previous Year = TOTALYTD(SUM(Sales[Revenue]),DATEADD('Calendar-1'[Date],-1, YEAR))

[Baskar]

1. Bookings (ThisYear) = SUM(Table[Bookings])

2. Bookings (LastYear) = Calculate ( SUM(Table[Bookings]), SAMEPERIODLASTYEAR(Calendar[Date]))

let me know if it is not helping u