- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
YTD and Rest based on a hotel reservation date
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
____________
⭐️ Fabric Group Channel
⭐️ Microsoft Fabric Community
Please join the Power BI UX/UI User Group if you need help with dashboard design and usability
Join to Data Governance User Group
Join to DENEB and Power BI Enthusiasts User Group
Join to Data Fabric Best Practices User Group
Subscribe to my medium blog
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
____________
⭐️ Fabric Group Channel
⭐️ Microsoft Fabric Community
Please join the Power BI UX/UI User Group if you need help with dashboard design and usability
Join to Data Governance User Group
Join to DENEB and Power BI Enthusiasts User Group
Join to Data Fabric Best Practices User Group
Subscribe to my medium blog
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ?

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
09-04-2024 11:32 PM | |||
10-02-2023 09:25 AM | |||
07-21-2024 05:50 AM | |||
09-04-2024 03:05 AM | |||
07-26-2024 02:17 PM |