Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello PBI Community,
I'm new to Power BI and I've been trying to create a dashboard for our company to use. As I'm working for a Destination Management Company, the report is normally summarized based on arrival dates per seasonality ( or as we call it Business on the books ) as shown in the sample below ( last year's summary ). I have a 'Bookings' table and a 'Date' table where [InDate] is the related field to my 'Date' table. The goal is to make the same report (by arrival date ) but I want to show the previous week or month if desired based on the reservation date so that we can see the movement each week or month. I hope that makes sense. I appreciate any help in advance. Thank you.
Measure used.
Total Pax = SUMX( Bookings, Bookings[Adult] + ( Bookings[Child] / 2 ) )
I also use this measure in an Area Chart to show last year's.
Pax LY = CALCULATE( [Total Pax], SAMEPERIODLASTYEAR( Dates[Date] ))
This is the sample 'Bookings' table.
Solved! Go to Solution.
@justivan
This was actually abit more complicated than i initially understood 🙂 But i did some things and ths is the result:
Something i noticed, as you can see in the images above there is an increase in January that i didn't expect. This is because there are transactions like this:
Either way this is what i did,
First of all i created a duplicate of your Date table and made sure that this table did not have any active relationships:
Following this I changed ResDate in the matrix to Date_2[Date] and added a slicer on the same field:
Finally I changed the DAX on the Cumulative measure:
Cumulative Pax =
VAR mDate = MAX('Date _ 2'[Date])
Return
CALCULATE([PaxByInDate];
Bookings[ResDate] < mDate
)
Try this and get back to me, i hope we're on the right track!
Br,
J
Hi @amitchandak,
I looked into the links that you provided but I'm not really sure if those techniques are applicable to the output that I needed. As @tex628 mentioned, I need the [ResDate] in row context and columns are [InDate]. I also tried the measure provided by @tex628 but I didn't get the expected output. I'm attaching a sample .pbix which I hope will help to find a solution to my problem.
@justivan
This was actually abit more complicated than i initially understood 🙂 But i did some things and ths is the result:
Something i noticed, as you can see in the images above there is an increase in January that i didn't expect. This is because there are transactions like this:
Either way this is what i did,
First of all i created a duplicate of your Date table and made sure that this table did not have any active relationships:
Following this I changed ResDate in the matrix to Date_2[Date] and added a slicer on the same field:
Finally I changed the DAX on the Cumulative measure:
Cumulative Pax =
VAR mDate = MAX('Date _ 2'[Date])
Return
CALCULATE([PaxByInDate];
Bookings[ResDate] < mDate
)
Try this and get back to me, i hope we're on the right track!
Br,
J
Hi @tex628 ,
That looks more flexible than what I managed to put up. In regards to the unexpected increase in the previous months, you are right. There are a lot of records like that where some of our agents entered the data in the system at a much later time (we were surprised too and those agents will have some explaining to do as soon as I finish the report 😁).
I also tried some solution last night and ended up creating another calendar table ('RCalendar') except that I linked that to [ResDate]. So my Cumulative measure is something like this.
Cumulative Pax = CALCULATE( [Pax Count],
FILTER( ALLSELECTED( 'RCalendar' ),
'RCalendar'[Date] <= MAX( 'RCalendar'[Date] ) ))
And something like this to get the Pax Count 7 Days prior ( got it from a tutorial series that I watched ) that I used with Area and Bar chart.
Pax Count LW =
CALCULATE ( [Pax Count],
FILTER( ALL('RCalendar'),
'RCalendar'[Year] = SELECTEDVALUE( 'RCalendar'[Year] ) &&
'RCalendar'[Date] = SELECTEDVALUE( 'RCalendar'[Date] ) - 7))
I will try to implement your solution as soon as I get home to give you feedback. ( desktop here in the office can barely run Power BI 😅)
From what i can tell your solution should be able to work aswell, let me know how it goes!
Br,
J
Yeah. It pretty much does the same with or without the relationship as long as you use the correct date ( ResDate or Date from the calendar ). Except that my measure is incorrect because of the filter. As soon as I apply a filter it will only get the cumulative pax on the given dates and will not include the previous dates in the calculation. Just needed to put '<=' to get the correct figures as shown below. The first one is '<' only so it only calculates until the previous day.
Thanks a lot for your help. Now I have a foundation to work on to start adding new measures.
Hi @amitchandak and @tex628
Thanks for your responses.
I'm sorry but I think I wasn't clear enough on what output I need. So basically, the way the report will be shown is still based on 'Arrivals' per month but we need to see the movement ideally per week. I did a sample output in excel. Say for example today is Sep 8, 2019, we have the below figures with a total of 324,509. 1 week prior, we had 313,109. So the added figure was roughly 11K that's spread from September to December arrivals. No changes in prior months as those are actuals already since it's in the past. So basically it's like showing the accumulated data.
I have this measure from a tutorial that I watched where I got the idea. Except this one was only based on 'Order Date' and 'Totals'. Whereas, what my intended output is to show the monthly arrivals based on reservation date.
Cumulative Sales = CALCULATE( [Total Sales],
FILTER( ALLSELECTED( Dates ),
Dates[Date] <= MAX( Dates[Date] ) ))
Does it make sense to create two calendar tables where one is for arrival date and one is for reservation date? I'm thinking of trying it once I get home.
Hi @justivan ,
If I understand your requirement correctly it seems like all you need to do is use ResDate as the row dimension in the matrix and then alter the measure just a little bit:
Cumulative Sales = CALCULATE( [Total Pax],
FILTER( ALLSELECTED( Dates ),
Dates[Date] <= MAX( Bookings[ResDate] ) ))
What result are you getting with this?
Br,
J
Same dates dimension you have to join with both dates(date without timestamp). One join will be inactive. And then as per need use userelation to activate the join. I have given an example and link in the last update.
One more case when we want one formula to run on both dates. We can have a slicer from the other date too. Typically dates allow you date hierarchy. But most needed we can have two date dimensions.
Bit different but usage of two date dimensions
Comparing-Data-Across-Date-Ranges
For a previous week you have to create a week before measure
Example
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-7,day))
To use another date you can join that date also with date dim. the relation would be inactive. You can make that active by using use relation . example
Hired Employee = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[Start Date],'Date'[Date]) )
Terminated Employees = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[End Date],'Date'[Date]))
refer
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
Hello @justivan ,
You can use the USERELATIONSHIP() dax syntax to temporarily swap between relationships in measures. You just need to make an inactive relationship.
Like this:
Sales = SUM('Project'[Amount])
Sales_StartDate = CALCULATE([Sales];USERELATIONSHIP('Project'[StartDate] ; 'Calendar'[Date])
Sales_EndDate = CALCULATE([Sales];USERELATIONSHIP('Project'[EndDate] ; 'Calendar'[Date])
This should allow you to make seperate calculations for your reservation date!
Br,
J
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
87 | |
84 | |
65 | |
49 |
User | Count |
---|---|
140 | |
114 | |
110 | |
59 | |
59 |