Forum Discussion
Helper IIIHow to calculate prorate value
Hi All,
How to calculate prorate value in Power BI
Truck Travel Start Date Travel End Date Expense(Amount)
G1 25-Dec-2016 06-Apr-2017 45000
G2 06-Jan-2017 15-Feb-2017 7500
G3 01-Mar-2017 09-Jan-2018 83600
Formula:-
Prorate value : (Prorate days / Travel Days) * Expense (Amount) --------------------------------------------------------
1. Travel Days : Travel End Date - Travel Start date 2. Prorate Days : If [ (Prorate End Date - Prorate Start Date)>0, (Prorate Start Date - Prorate End Date), 0] ------------------------------------------------------------------------------------------------------------
2A. Prorate Start Date: If [Travel Start Date > Filter Start date, Travel Start Date , Filter start date] 2B. Prorate End Date : If [Travel End Date < Filter End Date , Travel End Date , Filter End Date]
Filter Start Date (min date value of filter) and Filter End Date (max date value of filter) should contain dynamic date value based on "between date slicer" (Filter Range: 01-June -2016 to 31-May -2018 and it is an independent column).
Please provide solution for prorate value.
By
Gnanasekar
8 Replies
v-chuncz-msftCommunity Support
You may add a measure as shown below.
Measure = VAR s = MAX ( Table1[Travel Start Date] ) VAR e = MAX ( Table1[Travel End Date] ) VAR fs = MIN ( 'Calendar'[Date] ) VAR fe = MAX ( 'Calendar'[Date] ) VAR ps = MAX ( s, fs ) VAR pe = MIN ( e, fe ) RETURN DIVIDE ( IF ( ps <= pe, DATEDIFF ( ps, pe, DAY ) + 1, 0 ), DATEDIFF ( s, e, DAY ) + 1 ) * MAX ( Table1[Expense(Amount)] )
GnanasekarMeasure is giving correct prorate value when data present in row(truck) wise. ( Table visual)
But, It is giving wrong prorate value when data was grouped by any other column. (bar visual)
Eg: I have one more column Truck model ( Scania, Volvo). truck G1, G2, G3 is Scania , truck G4,G5,G6 is Volvo.
I checked value in row wise, prorate value is correct. But if I group by truck model , measure is giving wrong value.
Truck model : Scania
Measure is taking which is maximum travel start date & maximum travel end date of ' Scania truck model' in particular filter range.
So prorate value is wrong.
Measure = VAR s = MAX ( Table1[Travel Start Date] ) VAR e = MAX ( Table1[Travel End Date] )Here measure is looking entire dataset of truck model in that filter range. Not in row (truck) wise.
By
Gnanasekar
- Gnanasekar
I have attached prorate value file here.
There truck wise we are getting correct prorate value. But if truck model wise , it is showing wrong value.
(Due to measure behaviour, it is showing that. But we don't need that incorrect prorate value. We need summation of row wise value in total)
How to get summation of row(truck) wise value in grouping(truck model) time?
By
Gnanasekar
- bsas
Post Patron
Hi Gnanasekar,
I think as max and min you can use:
filter start date = date(2016,6,1) filter end date = date(2018,5,31)
Prorate Start Date: IF(travel start date > filter start date, travel start date , filter start date)
Prorate End Date : If(travel end date < filter end date , travel end date , filter end date)
Travel Days = Travel End Date - Travel Start date //format for this columns should be number
Prorate Days : IF((Prorate End Date - Prorate Start Date)>0, (Prorate Start Date - Prorate End Date), 0)
Prorate value = (Prorate days / Travel Days) * Expense(Amount)- Gnanasekar
Hi bsas
Filter Start Date and Filter End Date wont be a fixed value. It should be change based between date slicer.
Eg:
If slicer selected range : 01-June-2016 to 31-May-2018
then,
Filter Start Date : 01-June-2016
Filter End Date : 31-May-2018
If slicer selected range : 07-Feb-2017 to 31-Mar-2017
then,
Filter Start Date : 07-Feb-2017
Filter End Date : 31-Mar-2017
If slicer selected range : 19-Mar-2017 to 21-Mar-2017
then,
Filter Start Date : 19-Mar-2017
Filter End Date : 21-Mar-2017
and more combination...
Based in slicer selected range, Filter Start Date and Filter End Date will be change.
By
Gnanasekar
- bsas
Than use MIN filter start date and MAX for filter end date.
If this doen't work then you should create measure using calculate(sum(),filter(allselected(), and(date>min(filter start date), date<Max(filter end date))) (e.g. just example, you can find clear version bia searh in this forum)
- vcastello
Resolver III
Hi Gnanasekar
Why don't you try .... From end to beginning ...2A. Prorate Start Date:
IF ( ['TableName'[Travel Start Date] > MIN(TableName[Travel Start Date]),
Travel Start Date ,
MIN(TableName[Travel Start Date])
)
2B. Prorate End Date :
IF ( ['TableName'[Travel End Date] < MAX(TableName[Travel End Date]),
Travel End Date ,
MAX(TableName[Travel End Date])
)
2. Prorate Days :
IF (
[Prorate End Date] - [Prorate Start Date]>0,
([Prorate Start Date] - [Prorate End Date]),
0
)
1. Travel Days :
'TableName'[Travel End Date] - 'TableName'[Travel Start date]
Prorate value : DIVIDE([Prorate Days],[Travel Days]) * 'TableName'[Expense]
Anyway, you should check the 'spelling' of '(' and '[' ']' and use the table names if possible
Hope That Helps
Vicente- Gnanasekar
Travel date should compare with filter date not travel date.
In
2A. Prorate Start Date :
IF ( ['TableName'[Travel Start Date] >
MIN(TableName[Travel Start Date]), (It is filter start date)
Travel Start Date ,
MIN(TableName[Travel Start Date]))and
2B. Prorate End Date :
IF ( ['TableName'[Travel End Date] <MAX(TableName[Travel End Date]), (It is filter end date)
Travel End Date ,
MAX(TableName[Travel End Date]) )Here,
Unable to compare column (Travel start/end date) with min/max measure (Filter start/end date).
That is big problem here.
By
Gnanasekar