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
8 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