Forum Discussion
progress line chart based on days to destination
Hi,
We are in the process of developing a shipping status dashboard wherein we would like to place a line chart visual which will progress according to days covered.
say the shipment takes 10 days to reach destination , shipment start date is 01/05/2023 and date of arrival at destination in 10/05/2023. so on day 1 ( i.e. 01/05/2023 the line chart should be at lowest level and basedon each progressing date it should move upwards or rightwards. how can we make this happen
looking for your guidance
regards,
dsmitha
If you have a table similar to this:
Delivery
Start End Product 5/1/2023 5/10/2023 A 5/3/2023 5/20/2023 B 5/13/2023 5/19/2023 C And a Dates table with the relationships set up:
(Active) Dates[Date] --1:M--> Delivery[Start]
(Inactive) Dates[Date] --1:M--> Delivery[End]Then you can use the following measure to add a line for each product in a continuous line chart visual:
Measure = VAR _start = IF( NOT ISEMPTY( Delivery ), 0 ) VAR _end = CALCULATE( IF( NOT ISEMPTY( Delivery ), 1 ), USERELATIONSHIP( Dates[Date], Delivery[End] ) ) RETURN COALESCE( _start, _end )If you need your Date axis to be categorical, the following measure can be used instead (in this scenario you could just remove relationship between Dates and Delivery, and in which case would not need to include REMOVEFILTERS):
Measure 2 = VAR _curDay = MIN( Dates[Date] ) VAR _categoryStart = CALCULATE( MIN( Delivery[Start] ), REMOVEFILTERS( Dates ) ) VAR _categoryEnd = CALCULATE( MAX( Delivery[End] ), REMOVEFILTERS( Dates ) ) VAR _deliveryDates = DATESBETWEEN( Dates[Date], _categoryStart, _categoryEnd ) VAR _inDeliveryTimeframe = _curDay IN _deliveryDates VAR _completeProportion = DIVIDE( _curDay - _categoryStart, _categoryEnd - _categoryStart ) RETURN IF( _inDeliveryTimeframe, _completeProportion )
2 Replies
- MarkLaf
Super User
If you have a table similar to this:
Delivery
Start End Product 5/1/2023 5/10/2023 A 5/3/2023 5/20/2023 B 5/13/2023 5/19/2023 C And a Dates table with the relationships set up:
(Active) Dates[Date] --1:M--> Delivery[Start]
(Inactive) Dates[Date] --1:M--> Delivery[End]Then you can use the following measure to add a line for each product in a continuous line chart visual:
Measure = VAR _start = IF( NOT ISEMPTY( Delivery ), 0 ) VAR _end = CALCULATE( IF( NOT ISEMPTY( Delivery ), 1 ), USERELATIONSHIP( Dates[Date], Delivery[End] ) ) RETURN COALESCE( _start, _end )If you need your Date axis to be categorical, the following measure can be used instead (in this scenario you could just remove relationship between Dates and Delivery, and in which case would not need to include REMOVEFILTERS):
Measure 2 = VAR _curDay = MIN( Dates[Date] ) VAR _categoryStart = CALCULATE( MIN( Delivery[Start] ), REMOVEFILTERS( Dates ) ) VAR _categoryEnd = CALCULATE( MAX( Delivery[End] ), REMOVEFILTERS( Dates ) ) VAR _deliveryDates = DATESBETWEEN( Dates[Date], _categoryStart, _categoryEnd ) VAR _inDeliveryTimeframe = _curDay IN _deliveryDates VAR _completeProportion = DIVIDE( _curDay - _categoryStart, _categoryEnd - _categoryStart ) RETURN IF( _inDeliveryTimeframe, _completeProportion )