DAX measure to filter based on 2 dates

[justivan]

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.

[justivan]

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 .

[tex628 · Accepted 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

[justivan]

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.

[tex628]

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

Br,
J

[justivan]

@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.

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

[tex628]

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

[justivan]

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

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.

[tex628]

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

[amitchandak]

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

[amitchandak]

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

[tex628]

 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