Forum Discussion
progress line chart based on days to destination
- 3 years ago
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 )
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 )
hi MarkLaf,
thank you very much, your solution is spot on
double kudoz
regards,
dsmitha