Diagonal Calculation, Dividing one month by prior month but using a different account

[Anonymous]

Hi there,

Here is a simple view of the sample data

I want to create a measure in PBI that for account B in Month 2 I divide that by Account A in Month 1 (which equals 2).  Then i want to carry that on for month 3 and divide 300/200 (Account C month 3 by Account B month 2).

I have been scratching my head how to figure this out.  Can anyone please help?

Thanks!

GZ

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

Just try this formula:

Measure 3 = 
VAR _lastvalue =
    CALCULATE (
        MAX ( 'Table'[Month] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Month] < MAX ( 'Table'[Month] ) )
    )
RETURN
    DIVIDE (
        CALCULATE ( SUM ( 'Table'[Value] ) ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Month] = _lastvalue )
        )
    )

Result:

here is pbix file, please try it.

Best Regards,

Lin

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

Just try this formula:

Measure 3 = 
VAR _lastvalue =
    CALCULATE (
        MAX ( 'Table'[Month] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Month] < MAX ( 'Table'[Month] ) )
    )
RETURN
    DIVIDE (
        CALCULATE ( SUM ( 'Table'[Value] ) ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Month] = _lastvalue )
        )
    )

Result:

here is pbix file, please try it.

Best Regards,

Lin

[Anonymous]

Hi there,

This works for the smaller data set but if i expand it a bit it doesn't.  The issue is that the account changes.  Please see below when I use your measure.  See below on what the measure looks like if i expand the data set.

The calculation should actually be the below (this one done in excel)

Thanks for your help!

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

If so, you need to add an index/rank column for Account column to determine which Account is prior of the same month.

Otherwise how do you know the prior account of "B" is "A" not "D".

So adjust the formula as below:

Step1:

Create an index/rank column for Account column.

Step2:

Measure 3 = 
VAR _lastvalue =
    CALCULATE (
        MAX ( 'Table'[Month] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Month] < MAX ( 'Table'[Month] ) )
    )
var _lastAccount=
    CALCULATE (
        MAX ( 'Table'[Account] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] < MAX ( 'Table'[Index]) )
    )
RETURN
    DIVIDE (
        CALCULATE ( SUM ( 'Table'[Value] ) ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Month] = _lastvalue &&'Table'[Account]=_lastAccount)
        )
    )

Result:

 

here is new pbix file, please try it.

Best Regards,

Lin