Forum Discussion

justivan's avatar
justivan
Icon for Helper II rankHelper II
6 years ago
Solved

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 ...
Need Help
  • tex628's avatar
    tex628
    6 years ago

    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



tex628's avatar
tex628
Icon for Community Champion rankCommunity Champion
6 years ago

 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