Compare Feature Sales with Previous Year Feature Sales

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

Hi Anonymous, 

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?

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

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

Hi Anonymous,

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:

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

Dear Anonymous,

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.