Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BatsBeek
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 TimeSinceNewMonthyear
19509201705
19595201706
19688201707
19793201708
19907201709
20030201710
20166201711
20210201712

 

 

 

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 TimeSinceNewMonthyearTime used per month
195092017050
1959520170686
1968820170793
19793201708105
19907201709114
20030201710123
20166201711136
2021020171244

 

Any suggestions on how this can be done?

9 REPLIES 9
Zubair_Muhammad
Community Champion
Community Champion

Hi @BatsBeek

 

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

 

 


Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad

 

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.

@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 )
            )
    )

Regards
Zubair

Please try my custom visuals

Hi @Zubair_Muhammad,

 

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.

 

 

Hi @BatsBeek

 

Are there duplicate MonthYear in your dataset?

 

If Yes, Could you paste an extended dataset please?


Regards
Zubair

Please try my custom visuals

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
20170718829
20170718830
20170718831
20170718832
20170718833
20170818833
20170818834
20170818836
20170818837
20170818840
20170818841
20170818843
20170818844
20170818845
20170818847
20170818898
20170818899
20170918900
20170918901
20170918902
20170918903
20170918904
20170918905
20170918906
20170918907
20170918908
20170918911
20170918945
20170918947
20170918948
20171018949
20171018950
20171018951
20171018952
20171018953
20171018954
20171018955
20171018957
20171019009
20171019011
20171019013
20171119014
20171119018
20171119019
20171119020
20171119023
20171119024
20171119036
20171119038
20171119039
20171119069
20171119070
20171119071
20171119073
20171119074
20171219075
20171219076
20171219078
20171219080
20171219100
20171219102
20171219105
20171219106
20171219107
20171219108
20171219109
20171219110
20171219111

HI @BatsBeek

 

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

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.