DAX Calculated Column for Comparing Values on Non-Consecutive Dates In the matching context

[likvors_husband]

Hi everyone,

I'm working on a DAX calculation in Power BI where I need to compare a value on a given date with the value on the previous date for the same context of Product, Partner, Projection, and Element. However, the dates are not always consecutive and have duplicates*, so I'm using an Index column based on the date to help with this comparison.

In my example table, I'm encountering an issue where the rows with Index 13 and Index 32 are not recognized as the previous value.

edit*

PreviousValue =
VAR PreviousValue = 
    CALCULATE(
        SUM(FactData[Value]),
        TOPN(
            1,
            FILTER(
                ALLSELECTED('FactData'),
                'FactData[Product] = EARLIER('FactData'[Product]) &&
                'FactData'[Partner] = EARLIER('FactData'[Partner]) &&
                'FactData'[Element] = EARLIER('FactData'[Element]) &&
                'FactData'[Projection] = EARLIER('FactData'[Projection]) &&
                'FactData'[Index] < EARLIER('FactData'[Index])
            ),
            'FactData'[Index],
            DESC
        )
    )
RETURN
PreviousValue

Many thanks in advance for your help!

Index	Date	Product	Partner	Element	Value	Projection	PreviousValue
1	08-Jan-24	Product1	Partner1	Element1	72	01-Apr-24
2	15-Jan-24	Product1	Partner1	Element1	72	01-Apr-24	72
3	22-Jan-24	Product1	Partner1	Element1	72	01-Apr-24	72
4	29-Jan-24	Product1	Partner1	Element1	72	01-Apr-24	72
5	05-Feb-24	Product1	Partner1	Element1	50	01-Apr-24	72
6	12-Feb-24	Product1	Partner1	Element1	50	01-Apr-24	50
7	19-Feb-24	Product1	Partner1	Element1	50	01-Apr-24	50
8	26-Feb-24	Product1	Partner1	Element1	50	01-Apr-24	50
9	04-Mar-24	Product1	Partner1	Element1	50	01-Apr-24	50
10	11-Mar-24	Product1	Partner1	Element1	50	01-Apr-24	50
11	18-Mar-24	Product1	Partner1	Element1	50	01-Apr-24	50
12	25-Mar-24	Product1	Partner1	Element1	50	01-Apr-24	50
13	02-Apr-24	Product1	Partner1	Element1	48	01-Apr-24	50
19	13-May-24	Product1	Partner1	Element1	96	01-May-24
20	20-May-24	Product1	Partner1	Element1	96	01-May-24	96
32	29-Jul-24	Product1	Partner1	Element1	96	01-Aug-24
33	31-Jul-24	Product1	Partner1	Element1	96	01-Aug-24	96
34	01-Aug-24	Product1	Partner1	Element1	96	01-Aug-24	96
35	05-Aug-24	Product1	Partner1	Element1	96	01-Aug-24	96
36	13-Aug-24	Product1	Partner1	Element1	96	01-Aug-24	96
37	19-Aug-24	Product1	Partner1	Element1	96	01-Aug-24	96

[likvors_husband · Accepted solution]

Thank you, everyone, for your input! I wanted to share that I found the solution I was looking for right here on the forum. The method to compare values across non-consecutive rows was simpler than I thought and was already discussed in this thread. I hope this helps someone in the future as it did for me. Cheers!

[DataNinja777]

Since the date column doesn't seem to contain duplicates, it appears that we may not need to use the index column.

The resulting output is shown below. 

Please let me know if this meets your expectations.

I have attached an example PBIX file for your reference.

[likvors_husband]

Thank you, DataNinja777! However, the date column does contain duplicates, which is why I included the index column. Apologies for not mentioning that earlier—I completely overlooked it.