Add a column which shows difference of rows in a previous column with max values

[BatsBeek]

Hi,

Pretty new to PBI but having trouble using a simple excel formula in PBI.

I have the following data in PBI:

Max of TimeSinceNew	Monthyear
19509	201705
19595	201706
19688	201707
19793	201708
19907	201709
20030	201710
20166	201711
20210	201712

The first column only shows max values as during a month multiple submissions are made to the database, however only the month end usage is of interest to calculate the total time the object was used during a month.

I want to add a column showing this usage per month by calculating the difference between the rows of the max TimeSinceNew column (here done in excel):

Max of TimeSinceNew	Monthyear	Time used per month
19509	201705	0
19595	201706	86
19688	201707	93
19793	201708	105
19907	201709	114
20030	201710	123
20166	201711	136
20210	201712	44

Any ideas on how to do this in PBI?

[v-sihou-msft]

@BatsBeek

You can create a calculated column like below: 

Difference =
IF (
    CALCULATE (
        SUM ( Table1[Max of TimeSinceNew] ),
        FILTER ( Table1, Table1[Monthyear] = EARLIER ( Table1[Monthyear] ) - 1 )
    )
        = 0,
    0,
    Table1[Max of TimeSinceNew]
        - CALCULATE (
            SUM ( Table1[Max of TimeSinceNew] ),
            FILTER ( Table1, Table1[Monthyear] = EARLIER ( Table1[Monthyear] ) - 1 )
        )
)

Regards,

[BatsBeek]

Hi @v-sihou-msft,

Thanks, this formula seems to come close, however PBI says the column 'Max of TimeSinceNew' cannot be found or may not be used in the table. The column TimeSinceNew has multiple values for each Monthyear, but as I am only intereseted in the final usage at the end of the month I use the Max filter. 

Is there a way to create the column showing the differences between the Max values of TimeSinceNew per Monthyear?

Best regards