Forum Discussion

Gnanasekar's avatar
Gnanasekar
Icon for Helper III rankHelper III
9 years ago

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...
Need Help
Tips and Tricks
Tutorial Requests
v-chuncz-msft's avatar
v-chuncz-msft
Icon for Community Support rankCommunity Support
9 years ago

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)] )
  • Gnanasekar's avatar
    Gnanasekar
    Icon for Helper III rankHelper III
    9 years ago

    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

  • Gnanasekar's avatar
    Gnanasekar
    Icon for Helper III rankHelper III
    9 years ago

    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