Finding Change in Start Date Between Snapshots
Hello, I've been trying to create a calculated column in my workbook that calculates the change in start date for each Activity ID since the last snapshot was taken. Snapshots are taken on the first of every month, and activities could move forward, backward, stay the same, be completed or cancelled, or be newly added in each snapshot. I can't figure out how to create this formula in DAX, so I was wondering if someone would be able to help me out? I've included a sample image of what I'm going for below with the highlighted column being the one I'm trying to create. I really appreciate any help anyone can offer!Count rows with filtered dates
I am trying to create a measure with a count of people with graduation dates within a specific date range (June 2022 - Sep 2022) so they can be recognized at our event. In this table the graduation date is BTM_End_Date. There are no other tables involved. When I filter dates manually in the table view with datesbetween on or after 06/01/2022 and on or before 09/01/2022 I get 20 records. I have tried the following: BTM_within_90_days = CALCULATE(COUNTROWS('pre-event'), FILTER('pre-event', 'pre-event'[BTM_End_Date] >= 06/01/2022 & 'pre-event'[BTM_End_Date] < 09/01/2022 )) I get the result : MdxScript(Model) (4, 94) Calculation error in measure 'pre-event'[BTM_within_90_days]: DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values. If I remove the & 'pre-event'[BTM_End_Date] < 09/01/2022 I get the result of 82 which is the total number of people with any graduation date. If I try BTM_within_90_days = CALCULATE(COUNTROWS('pre-event'), filter('pre-event', 'pre-event'[BTM_End_Date] <= 09/01/2022)) I get a result of 283 which is the number of blank records. I checked this by running BTM_within_90_days = CALCULATE(COUNTROWS('pre-event'), filter('pre-event', ISBLANK('pre-event'[BTM_End_Date]))) I have also tried : BTM_within_90_days = COUNTAX('pre-event', DATESBETWEEN('pre-event'[BTM_End_Date], 06/01/2022, 09/01/2022)) Which returns (BLANK) In the screenshot above, I show that the column I am using is formatted as a short date. Any help is greatly appreciated.
How to measure cumulative active project hours from start and end dates?
I have a table of active projects, each with their name, ID, start date, end date, and assigned hours per day. I want to create a graph looking ahead 6+ months of all the hours we are expecting these projects to take up. Right now, I'm building a table starting with a column of every working day from the beginning of 2021 to mid 2022. I'd like to take each of these dates, and go through the whole table of active projects looking for all projects that have start dates before or on the work date and end dates after on on the work date, then sum all of the expected hours per day of these projects. How can I accomplish this?
