Forum Discussion
Helper IIDAX measure to filter based on 2 dates
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
Helper IIHi 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.
Community Championjustivan
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
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.