Forum Discussion

asl10's avatar
asl10
Icon for Helper I rankHelper I
9 years ago

Compare Feature Sales with Previous Year Feature Sales

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 fo...
Need Help
Baskar's avatar
Baskar
Icon for Resident Rockstar rankResident Rockstar
9 years ago

Share some sample data , i will help u to achieve your goal

asl10's avatar
asl10
Icon for Helper I rankHelper I
9 years ago

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 DateReservation DateRevenue
01/01/201502/02/2016250
10/05/201503/04/2016100
11/11/201510/12/2016300
27/12/201511/11/2016500
03/04/201610/10/2017100
05/10/201605/05/2017400
20/12/201623/12/2017585
23/12/201610/10/2017100

 

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's avatar
    parry2k
    Icon for Super User rankSuper User
    9 years ago

    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's avatar
      parry2k
      Icon for Super User rankSuper User
      9 years ago

      This will be your formula for previous year YTD

       

      mYTD Previous Year = TOTALYTD(SUM(Sales[Revenue]),DATEADD('Calendar-1'[Date],-1, YEAR))
    • asl10's avatar
      asl10
      Icon for Helper I rankHelper I
      9 years ago
      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...
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      asl10
      Icon for Helper I rankHelper I
      9 years ago

      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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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