Creating a column showing deltas of rows which display MAX values

[BatsBeek]

Hi all,

I have the following vizualisation in PowerBI Desktop:

What I am actually interested in is showing the deltas for each month, so March should show 8442-8369=73. Some clarifications regarding the data:

• I have data from before 2017 to determine the January delta, however to not overcomplicate this question I limited the year to 2017.
• Data is in DirectQuery mode
• The values you see in the vizualisation in the Max of TimeSinceNew column are the highest values observed for the months for a variable in the original table called TimeSinceNew. The database receives multiple updates during a month and therefore multiple observations for TimeSinceNew per month. I am however merely interested in comparing the highest value of TimeSinceNew for the month with the highest value of the previous month to accurately show usage during that month. This is why, in the Values area of editing the visualization I choose MAX of TimeSinceNew, rather than for example AVERAGE or SUM. I think this is complicating the creation of the column showing the deltas, hence why I mention it.
• The column showing the months is created with the group function of Power BI for variable 'Dates' in the original table. For example: Month january received the following data inputs which are in the original table powering this visualization (this also explains the previous point of the MAX values):

  Date	TimeSinceNew
  43101	8285
  43115	8293
  43121	8305
  43131	8315

  The original table (called Measurements), of which you see an excerpt here, contains other variables as well. I left them out however to limit the problem to the columns I want to create the deltas for. To create the visualisation the dates are grouped per month and TimeSinceNew in the visuals "Values" area is set to Maximum (not sure if any of this matters, just trying to be as transparant as possible).

I tried the following (from this thread😞

Column = 
VAR lastDateOfPreviousMonth =
LASTNONBLANK ( PREVIOUSMONTH ( Measurements[Date (bins) 2] ); 1 )
RETURN
IF (
ISBLANK ( lastDateOfPreviousMonth );
BLANK ();
Measurements[TimeSinceNew]
- CALCULATE (
MAX ( Measurements[TimeSinceNew] );
FILTER (
ALL ( Measurements );
Measurements[Id] = EARLIER ( Measurements[Id] )
&& Measurements[Date (bins) 2] = lastDateOfPreviousMonth
)
)
)

This leads to the following error: "Function 'PLACEHOLDER' is not allowed as part of calculated column DAX expressions or DirectQuery models."

I also tried:

Difference = 
IF (
    CALCULATE (
        SUM ( Measurements[TimeSinceRepair] );
        FILTER ( Measurements; Measurements[Date (bins)] = EARLIER ( Measurements[Date (bins)] ) - 1 )
    )
        = 0;
    0;
    Measurements[TimeSinceRepair]
        - CALCULATE (
            SUM ( Measurements[TimeSinceRepair] );
            FILTER ( Measurements; Measurements[Date (bins)] = EARLIER ( Measurements[Date (bins)] ) - 1 )
        )
)

This returns the error: "Function 'Calculate' is not allowed as part of calculated column DAX on DirectQuery models." 

Is there a formula which allows me to create the column showing the deltas in the visualisation, even though I am in DirectQuery mode? If this needs to be done in the query editor: also works for me just tell me the formula to enter there and I will try. Ultimately I don't even need the original TimeSinceNew column in the visual, just the deltas are of importance.

Thanks a lot for your time and help in advance!

[Zubair_Muhammad]

@BatsBeek

Could you try this MEASURE

Measure =
VAR PreviousMonthValue =
    CALCULATE (
        [Max of Time Since New],
        FILTER (
            ALL ( TableName ),
            YEAR ( TableName[Date of Bins] ) = YEAR ( MAX ( TableName[Date of Bins] ) )
                && MONTH ( TableName[Date of Bins] )
                    = MONTH ( MAX ( TableName[Date of Bins] ) ) - 1
        )
    )
RETURN
    IF (
        NOT ( ISBLANK ( PreviousMonthValue ) ),
        [Max of Time Since New] - PreviousMonthValue
    )

[BatsBeek]

@Zubair_Muhammad Thanks for your reply.

I tried your formula, adapting it by replacing TableName with the name of the table and the other column names as shown here:

Measure 2 = 
VAR PreviousMonthValue =
    CALCULATE (
        MAX(Measurements[TimeSinceNew];
        FILTER (
            ALL ( Measurements );
            YEAR ( Measurements[Date (bins) 2] ) = YEAR ( MAX ( Measurements[Date (bins) 2] ) )
                && MONTH ( Measurements[Date (bins) 2] )
                    = MONTH ( MAX ( Measurements[Date (bins) 2] ) ) - 1
        )
    )
RETURN
    IF (
        NOT ( ISBLANK ( PreviousMonthValue ) );
        MAX(Measurements[TimeSinceNew] - PreviousMonthValue
    )

If I don't adapt the part in your formula for [Max of Time Since New]  and [Date of Bins] the formula can not find values for these variables.

*Note: Max of TimeSinceNew in the first image provided in my original post is not a variable in the original table, merely the MAXIMUM value of the variable TimeSinceNew for a given month (which is a Date (bins) value).

I cannot create the collumn and I get the error "The Synax for 'RETURN' is incorrect. (DAX etc..................."

Any other suggestions?

[DPlankenhorn]

I am also experiencing the "Function 'PLACEHOLDER'" error for DAX statements using MAXX (MINX) / FILTER / RELATEDTABLE expressions after a data refresh operation.  Thought the error was resolved with the Feb 2019 update, but error resurfaced yesterday and still seeing this morning.  Please see my entry within the Forum for reference.

I have found that just by making an "immaterial" change to the DAX expression (e.g., Adding or removing an unnecessary space character at the end of the expression), the error clears UNTIL the next time I do a data refresh.