Search code examples
exceldaxpowerpivotpivot-chart

Limitation line Pivot area charts

I'm leasing a car, which I use my self, but also rent out for other people to use. I have 2000km I can drive each month, so I'm trying to do an area pivot graph which will track how much I use it vs how much it's rented out.

I have a table column consisting of the rented mileage and my own mileage:

 ___________________________________
|Date    |Rented mileage|Own mileage|
|23/03-18|           315|        117|
|07-04-18|           255|        888|
|07/04-18|           349|          0|
|13/04-18|           114|          0|
|21/04-18|           246|        113|
|28/04-18|          1253|          0|
|01/05-18|          1253|          0|

I've set up two filters which cummulative sums of each column which yields this Pivot area chart:

enter image description here

What I'm trying to do now is adding a limit line to show whether I'm above or below the average 2000km I can drive each month (only 1000km in march as I got the car in the middle of march). Something that should look like this:

enter image description here

I can't really figure out how to do that though. My first thought was a adding a new table with the following values and just add that to the Pivot Chart as a line.

 _______________________
|Date    |Allowed mileage|
|01/03-18|           1000|
|01-04-18|           2000|
|01/05-18|           2000|
|01/06-18|           2000|
|01/07-18|           2000|
|01/08-18|           2000|
|01/09-18|           2000|
|01/10-18|           2000|
|01-11-18|           2000|
|01/12-18|           2000|
|01/01-19|           2000|
|01/02-19|           2000|
|01/03-19|           1000|

But the Pivot Chart can't establish a relationship between the two tables, which on second thought makes sense!

I'm now looking into some sort of interpolative measure, but I can't figure out how to go about creating that.

Any input would be much appreciated! :)

PS: In case anyone is interested, I asked a related question about Cummulative sums here.

Solution

  • Create a Date table to use as a common axis and to relate your tables.

    Once you have that table, create a new column:

    Daily Target =
    LOOKUPVALUE( Allowed[Allowed Milage],
        Allowed[Date], EOMONTH('Date'[Date], -1) + 1 )
    / DAY( EOMONTH ( 'Date'[Date], 0 ) )

    This looks up the target for that month and then divides by the number of days in that month to give a daily target.

    Then the 3 measures you want to use are the following:

    Cumulative Rented Milage =
    CALCULATE (
        SUM ( Transactions[Rented Milage] ),
        FILTER ( ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] ) ) )

Cumulative Own Milage =
    CALCULATE (
        SUM ( Transactions[Own Milage] ),
        FILTER ( ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] ) ) )

Cumulative Own Milage =
    CALCULATE (
        SUM ( 'Date'[Daily Target] ),
        FILTER ( ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] ) ) )

    Plot these on a graph with 'Date'[Date] as your axis and do a line & stacked area combo chart.