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 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.
Community ChampionFrom 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.