cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
justivan
Helper II
Helper II

DAX measure to filter based on 2 dates

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] ))

Capture.PNG

This is the sample 'Bookings' table.

Capture2.PNG

 

1 ACCEPTED SOLUTION

@justivan 

This was actually abit more complicated than i initially understood 🙂 But i did some things and ths is the result:

image.pngimage.png

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: 

image.png

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:

image.png

Following this I changed ResDate in the matrix to Date_2[Date] and added a slicer on the same field:
image.png
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




Connect on LinkedIn

View solution in original post

11 REPLIES 11
justivan
Helper II
Helper II

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.

 

Sample.pbix .

@justivan 

This was actually abit more complicated than i initially understood 🙂 But i did some things and ths is the result:

image.pngimage.png

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: 

image.png

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:

image.png

Following this I changed ResDate in the matrix to Date_2[Date] and added a slicer on the same field:
image.png
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




Connect on LinkedIn

View solution in original post

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 😅

This is the initial report that I managed to put up last night.This is the initial report that I managed to put up last night.

From what i can tell your solution should be able to work aswell, let me know how it goes!

Br,
J


Connect on LinkedIn

@tex628 

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.

Had to add = to get the correct figures per date.Had to add = to get the correct figures per date.

Thanks a lot for your help. Now I have a foundation to work on to start adding new measures. 

@justivanHappy to help! Good luck on future endeavours! 🙂


Connect on LinkedIn
justivan
Helper II
Helper II

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.

Intended output to be shown in matrix and are chart for visualsIntended output to be shown in matrix and are chart for visuals

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] ) ))

 

 

Capture.PNG

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


Connect on LinkedIn

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

amitchandak
Super User IV
Super User IV

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

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

tex628
Super User II
Super User II

 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


Connect on LinkedIn

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors