start and end period

3 Topics

Create measure counting number of available resources at any given time
Hi guys. So, I have a table with information of cars location of any given time, where car movements are gives me a new row. CarId Location StartDate EndDate No. of days 1 A 1/1/2020 1/20/2020 20 1 B 1/20/2020 1/25/2020 5 1 A 1/25/2020 12/31/2020 340 2 C 10/15/2020 11/30/2020 46 2 A 11/30/2020 12/31/2020 32 What I want to calculate is the number of "Car days" available in various periods (per day, per month, per quarter etc). For example, I want the measure to say that at 1/1/2020 there were 1 Car day (since only Car 1 was available on Location A - Car 2 didnt "exist" at that point in time). Further, in January 2020 on Location A, there were 20 (row A) + 6 (row 3 - this location data is calculated by StartDate being 1/25/2020 but end of month is 1/31/2020) = 26 available car days. So, the issue I have is creating the logic where based on Min and Max dates i sum the number of days for each row, given the period I look at. If I evaluate per month basis, the Min(Date) is the 1st of the month and the Max(date) is the last day of the month. I do of course have a standard Date table. Thanks, Ruben
Solved
rubentj
3 years ago Place DAX Commands and Tips
1.8KViews
0likes
7Comments
Count IDS that transferred from one program to another based on start and end date columns
Hello! I can't seem to figure out how to set up the dax to calculate this (I'm relatively new to DAX). I'm hoping to create either a table visual or bar graph that I can filter using a Date filter to see how many IDs transferred from Program A to Program be in a given year. Below shows how my table is set up. A user/unique ID may have been in one of the programs more than once, but can not be in Program B while still in program A. If a user is still enrolled in a program, their end date is null. In this example, I am hoping to filter to the year 2020 and show that 2 users transferred from Program A to Program B (51001 and 51002). Row unique ID Program Name Start Date End Date 1 51001 Program A 6/1/2010 9/1/2012 2 51001 Program A 8/6/2014 6/30/2020 3 51001 Program B 7/2/2020 4 51002 Program A 4/1/2016 9/1/2020 5 51002 Program B 10/1/2020 5/1/2021 6 51003 Program A 4/1/2016 6/1/2018 7 51003 Program B 7/1/2018
Solved
Joacb761
5 years ago Place DAX Commands and Tips
567Views
0likes
1Comment
show date if falling between a start and an end date.
I have a data set with a few thousand line items. In this, there are 2 columns, a start date, and an end date. I am looking for a way to determine if an item was standing during a certain date. This is easy to do with a simple if statement, but the problem Im having is that I want to be able to filter it at the visual level by any date, selected by the user. I have [ActualBuildDate] and [ActualDismantleDate] Where [ActualBuildDate] will aways have a date, and [ActualDismantleDate] will alway be after [ActualBuildDate] or blank (Still standing). If I select January 2020, I want to see every item that was standing during January 2020. I have a Calender table set up using this code Date Filter = DATESBETWEEN( 'Date'[Date], MIN('1_Overview'[ActualBuildDate]), IF(MAX('1_Overview'[ActualDismantleDate]) > MAX('1_Overview'[ActualBuildDate]), MAX('1_Overview'[ActualDismantleDate]), MAX('1_Overview'[ActualBuildDate]) )) I have set a filter with a date heiarchy using this. but filtering by the dates on it do not return the proper items.
MattScruggs
5 years ago Place DAX Commands and Tips
847Views
0likes
2Comments