YTD and Rest based on a hotel reservation date

[asl10]

Hi,

Thank you all for your valuable support, tips and guides.

I works in the hotel insdustry and i already have a crystal report which shows  a table that compares the sales (based on reservation date) between two years on a YTD aspects asOf today and for REST months show the sales as reserved. Also for last year based on the asOf day today last year.

My question is as follows:

I m able to show the YTD and Previous YTD and Sales Last Year BUT i cannot show the rest (reserved sales) next months based on the date today and the same date last year.

Please advise.

[technolog · Accepted solution]

To display sales for the rest of the year after today's date (i.e., future sales) and compare it with the reserved sales from the same date onwards last year, you can leverage DAX in Power BI to construct these measures.

Here's a step-by-step approach to achieve this:

1. Date Table:

If you don't already have one, create a Date table that spans across all the possible dates in your dataset and beyond. This table should ideally contain a column for the Year, Month, Day, and other relevant date parts.
2. Create Relationships:

Establish a relationship between your reservation table and the Date table using the reservation date.
3. DAX Measures:

a. Current Year YTD Sales:

YTD Sales =
CALCULATE(
SUM(Reservations[Sales]),
DATESYTD('DateTable'[Date])
)
b. Last Year YTD Sales:

Previous YTD Sales =
CALCULATE(
[YTD Sales],
SAMEPERIODLASTYEAR('DateTable'[Date])
)
c. Reserved Sales For Remaining Months (Current Year):

Future Sales CY =
CALCULATE(
SUM(Reservations[Sales]),
FILTER(
ALL('DateTable'),
'DateTable'[Date] > TODAY() && YEAR('DateTable'[Date]) = YEAR(TODAY())
)
)
d. Reserved Sales For Remaining Months (Last Year):

Future Sales LY =
CALCULATE(
SUM(Reservations[Sales]),
FILTER(
ALL('DateTable'),
'DateTable'[Date] > SAMEPERIODLASTYEAR(TODAY()) && YEAR('DateTable'[Date]) = YEAR(SAMEPERIODLASTYEAR(TODAY()))
)
)
Use in Visuals:

Add these measures to your report visuals. You can then easily compare the YTD and reserved sales for the remaining months for both the current year and the previous year.
Remember to adjust the table and column names to match the ones you use in your Power BI model.

[Baskar]

I didn't get u clearly , based on my assumption if u select "Jan-16" u want to get the sales of "Feb-15" is it correct ?