Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Gnanasekar
Helper III
Helper III

How 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 8
v-chuncz-msft
Community Support
Community Support

@Gnanasekar,

 

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)] )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-chuncz-msft

 

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

Hi @v-chuncz-msft

 

Measure 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

vcastello
Resolver III
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

 

Hi @vcastello & @bsas

 

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

bsas
Post Patron
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)




 

 

 

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

@Gnanasekar

 

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)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.