cancel
Showing results for
Did you mean:
Frequent Visitor

## Add column showing difference of rows of another column with MAX values shown

Hi,

New to PBI and need some help with a function which can be easily done in excel but which I cannot figure out in PBI.

The following data is used:

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

Column 1 shows MAX values as only the end of month value is of interest when determining the time the object was used during a month.

I want to add a column showing the difference in these max values per month (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 suggestions on how this can be done?

9 REPLIES 9
Super User

## Re: Add column showing difference of rows of another column with MAX values shown

Try this Calculated Column if monthyear is formatted as dates

```Time =
VAR LastMonth =
PREVIOUSMONTH ( TableName[Month_Year] )
RETURN
IF (
NOT ( ISBLANK ( LastMonth ) ),
TableName[Max of TimeSinceNew]
- CALCULATE (
VALUES ( TableName[Max of TimeSinceNew] ),
FILTER ( ALL ( TableName ), TableName[Month_Year] = LastMonth )
)
)```

Super User

## Re: Add column showing difference of rows of another column with MAX values shown

@BatsBeek

If MonthYear is not formatted as date then may be this one

```Time =
VAR LastMonth =
MAXX (
FILTER ( TableName, TableName[MonthYear] < EARLIER ( TableName[Monthyear] ) ),
TableName[Monthyear]
)
RETURN
IF (
NOT ( ISBLANK ( LastMonth ) ),
TableName[Max of TimeSinceNew]
- CALCULATE (
VALUES ( TableName[Max of TimeSinceNew] ),
FILTER ( ALL ( TableName ), TableName[MonthYear] = LastMonth )
)
)```
Super User

Frequent Visitor

## Re: Add column showing difference of rows of another column with MAX values shown

Thanks for your reply, I think we are close to a solution. Monthyear is not formatted as a date as it is derived from other columns in the main dataset which link a month and year to an observation. It is created as year*100+month. When using your second solution the error shown is:

'A single value for column Monthyear in table TableName cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count or sum to get a single result.'

Any way to work around this? Thanks in advance for your help.

Super User

## Re: Add column showing difference of rows of another column with MAX values shown

Are there duplicate MonthYear in your dataset?

If Yes, Could you paste an extended dataset please?

Frequent Visitor

## Re: Add column showing difference of rows of another column with MAX values shown

I hope I am not going to overcomplicate it but yes there are.

Shown table would be for 1 object, which would report its TimeSinceNew multiple times during one month, so for each of these observations a MonthYear is created.

The complete database contains 1000s of objects, however I am limiting my table to 1 object for now to test out some reporting features (for which the time used during a month is the most crucial part).

Extended dataset for you to experiment on (here I turn off the 'Max of' filter for monthyear).:

Monthyear  TimeSinceNew

 201707 18828 201707 18829 201707 18830 201707 18831 201707 18832 201707 18833 201708 18833 201708 18834 201708 18836 201708 18837 201708 18840 201708 18841 201708 18843 201708 18844 201708 18845 201708 18847 201708 18898 201708 18899 201709 18900 201709 18901 201709 18902 201709 18903 201709 18904 201709 18905 201709 18906 201709 18907 201709 18908 201709 18911 201709 18945 201709 18947 201709 18948 201710 18949 201710 18950 201710 18951 201710 18952 201710 18953 201710 18954 201710 18955 201710 18957 201710 19009 201710 19011 201710 19013 201711 19014 201711 19018 201711 19019 201711 19020 201711 19023 201711 19024 201711 19036 201711 19038 201711 19039 201711 19069 201711 19070 201711 19071 201711 19073 201711 19074 201712 19075 201712 19076 201712 19078 201712 19080 201712 19100 201712 19102 201712 19105 201712 19106 201712 19107 201712 19108 201712 19109 201712 19110 201712 19111
Super User

## Re: Add column showing difference of rows of another column with MAX values shown

In that case, try this MEASURE (Not Calculated Column)

```Time =
VAR LastMonth =
MAXX (
FILTER (
ALL ( TableName ),
TableName[MonthYear] < SELECTEDVALUE ( TableName[Monthyear] )
),
CALCULATE ( MAX ( TableName[Monthyear] ) )
)
RETURN
IF (
NOT ( ISBLANK ( LastMonth ) ),
MAX ( TableName[TimeSinceNew] )
- CALCULATE (
MAX ( TableName[TimeSinceNew] ),
FILTER ( ALL ( TableName ), TableName[MonthYear] = LastMonth )
)
)```
Super User

Frequent Visitor

## Re: Add column showing difference of rows of another column with MAX values shown

I tried this, it says 'the function FILTER is not supported in this context in DirectQuery mode.' I'm sorry I never mentioned using this type of datasource before. Perhaps this only works when using a downloaded database?

Furthermore I managed to use the group function on the original dates to group (bins) the data per month, as I think this way it does read the column as dates. This however still does not work with any of the suggested formulas you gave before.

Thanks again for your help so far.

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### PBI Community Highlights

Check out what's new in the Power BI Community!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 59 members 1,290 guests
Recent signins: