Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |