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



amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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-trend/ba-p/882970

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