Please see message in Desktop Forum - CLOSED

[TrentS]

Good day all,

I am sure I am simply not finding the right function to accomplish this (or search on).

I have a simple % change measure that goes into a Visual Table.

Price_Change% =

var num = Calculate(Sum('Table'[PriceUSD]))

var denom = Calculate(Sum('Table'[PriceUSD]), PREVIOUSMONTH('Table'[Month]))

return IF(num, DIVIDE(num-denom,denom))

The sponsor wants a new field added to the visual that can change from month to month. This adds a new criteria to the row and we do not get a value because the row is no longer an exact match to the PREVIOUSMONTH. Works just fine if that new field is the same as the previous month.
What's the right function to be using so that the calculation ignores the new field and populates every row with that measure?

There are slicers/filters on the report page itself used to limit what displays in the table if that matters.

Thanks for your time in helping me out of my mental block!

Trent

UPDATE: Have added an example file. What am I missing?

[GilbertQ]

Hi @TrentS 

You could do something below which will look at the Max Date which is selected from the date table and then work out the previous month based on the Date selection

Previous Month Selection =

VAR MaxDateBlank =
    MAX( 'Date'[Date] ) 
// THis checks to see if the MAX Date from the Bet Table is blank for a particular row
// If it is blank then use the MaxDateBlank from above to put in a DATE Value.
// Otherwise the value for the Date will be blank which will break the LastDateDone
VAR MAxBetDate =
    IF(
        ISBLANK( MAX( 'Sales'[DayMarker] ) ),
        MaxDateBlank,
        MAX( 'Sales'[DayMarker] )
    )
VAR PrevMthStartDate =
    DATE ( YEAR ( MAxBetDate ), MONTH ( MAxBetDate ) - 1, 1 )
VAR PrevMthEndDate =
    DATE ( YEAR ( MAxBetDate ), MONTH ( MAxBetDate ) - 1, DAY ( MAxBetDate ) )
RETURN
    CALCULATE (
        [Sales Value],
        FILTER (
            'Date',
            'Date'[Date] >= PrevMthStartDate
                && 'Date'[Date] <= PrevMthEndDate
        )
    )

[TrentS]

Thanks for the reply Gilbert. Kudos given.
The Time Intelligence functions here don't seem to be an issue. It is more about having the measure ignore a field that is being added to the visual Table (and is a set column in the data table).
If I remove the new column from the visual, all rows populate correctly for that measure. With the column in the visual, only rows where that column is the same as the previous month populate.

In the example above, all four rows are for the same Product with different months (Oct-Jan). The bottom two rows have different values for that new column. (A product specification changes).
If I remove that new column all four rows populate with the values for the % change.

Keep the suggestions coming, please!