Flag Differences in Row vs Previous Row (based on Date, Site, and Person)

[kressb]

Hello,

I have a table set up like below.
Linked to a Site Key and a Date Dimension.

My goal is to come up with a formula for the "Change Indicator" column (currently a calculated column), where it will show a "1" if there's a change in any of the numbers for the SiteID+UniqueID2 compared to the month previous:

Date	SiteID	UniqueID2	Name	Num1	Num2	Num3	Num4	Num5	Num6	Total	Change Indicator
7/1/2020	A	3333	Person3	0.30	0.07	0.05	0.17	0.06	0.20	0.85	0
7/1/2020	B	3333	Person3	0.10	0.02	0.00	0.03	0.00	0.00	0.15	0
8/1/2020	A	3333	Person3	0.35	0.07	0.00	0.17	0.06	0.20	0.85	1
8/1/2020	B	3333	Person3	0.10	0.02	0.00	0.03	0.00	0.00	0.15	0
9/1/2020	A	3333	Person3	0.35	0.07	0.00	0.17	0.06	0.20	0.85	0
9/1/2020	B	3333	Person3	0.10	0.02	0.00	0.03	0.00	0.00	0.15	0
10/1/2020	A	3333	Person3	0.35	0.07	0.00	0.17	0.06	0.20	0.85	0
10/1/2020	B	3333	Person3	0.10	0.00	0.00	0.04	0.00	0.01	0.15	1
11/1/2020	A	3333	Person3	0.40	0.07	0.00	0.17	0.06	0.20	0.90	1
11/1/2020	B	3333	Person3	0.05	0.00	0.00	0.04	0.00	0.01	0.10	1

I started with just trying to get the difference based on "Num1" and once I had that right, I was going to work through Num2, Num3, etc. This is what I've tried:
Change Indicator = if(isblank(sumx(Table,Filter(Table,Table[UniqueID2]=Earlier(Table[UniqueID2]) && Table[Num1]<>Earlier(Table[Num1])))),1,0)
Error message: multiple columns cannot be converted to scalar values

Change Indicator = Table[Num1] - Lookupvalue(Table[Num1],Table[UniqueID2],[UniqueID2],Table[SiteID],[SiteID],Table[Date], DateAdd(Table[Date],-1,month))
This doesn't give me any error message to work off of, all column cells come back #ERROR.

Unfortunately I cannot provide any data files, etc, which I know is preferred.

If anyone would just point me in a general direction (links, examples, ideas), I would appreciate it.
I was reading about using index colums. Would that help at all? I didn't think I needed them since it's all based on the Date column.

TYIA.

[kressb · Accepted solution]

None of the replies I received worked, but this is what I figured out:
 To compare to previous month - use Measure:
    Change Indicator = if(min(Table[Date]<>Date(2020,07,01),
                                       If(Calculate(sum(Table[Num1]),PreviousMonth(Table[Date])) <> sum(Table[num1],
                                       1,0)

After I got this part working, I was asked to compare each month to the first month of the fiscal year (7/1/2020) rather than to the previous month. The only way I could get this to work was as a calculated column:

   Change Indicator = 
         Var _FirstDayFP = If(Table[FiscalPeriod]=2021, Date(2020,07,01),
                                      If(Table[FiscalPeriod]=2022, Date(2021,07,01),0))
         Return
         If(
           Table[Num1]
           <>
           Calculate(sum(Table[Num1]),
                           filter(Table,Table[Date]=_FirstDayFP),
                           filter(Table,Table[UniqueID2]=Earlier(Table[UniqueID2])),
                           filter(Table,Table[SiteID]=Earlier(Table[SiteID])),
                           filter(Table,Table[FiscalPeriod]=Earlier(Table[FiscalPeriod]))),
            1, 0)
Appreciate any feedback/comments/thoughts.
Hope this helps someone else.

[o_zavalnyuk]

here is the full DAX code

Change Indicator Calc = 
var num1 = 'Table'[Num1]
var num2 = 'Table'[Num2]
var num3 = 'Table'[Num3]
var num4 = 'Table'[Num4]
var num5 = 'Table'[Num5]
var num6 = 'Table'[Num6]
var dateT = 'Table'[Date]
var siteID = 'Table'[SiteID]
var filtertable = 
     FILTER(
        'Table';
        MONTH('Table'[Date]) = MONTH(dateT)-1
        && 'Table'[SiteID] = siteID
    )
var calcNum1 = 
    CALCULATE(
        COUNT('Table'[SiteID]);
        FILTER(
            filtertable;
            'Table'[Num1] <> num1
            || 'Table'[Num2] <> num2
            || 'Table'[Num3] <> num3
            || 'Table'[Num4] <> num4
            || 'Table'[Num5] <> num5
            || 'Table'[Num6] <> num6

        )
    )
var result = 
    SWITCH(
        TRUE();
        ISBLANK(calcNum1); 0;
        NOT(ISBLANK(calcNum1)); 1
    )
return result

[Ashish_Mathur]

Hi,

Can you live with this?  You may download my PBI file from here.

[kressb]

your file has num1, num2, num3 each in their own row
my powerbi table is set up exactly as in the question - num1, num2, num3 are columns within the same row, so i have to compare row to row, not within a column.
is there a way to use indexing or some other technique to compare row to row?

[Ashish_Mathur]

Hi,

I have applied transformations on your data using the Query Editor.  Please study my solution carefully.

[o_zavalnyuk]

Change Indicator Calc = 
var num1 = 'Таблица (2)'[Num1]
var num2 = 'Таблица (2)'[Num2]
var dateT = 'Таблица (2)'[Date]
var siteID = 'Таблица (2)'[SiteID]
var calcNum1 = 
CALCULATE(
    SUM('Таблица (2)'[Num1]);
    FILTER(
        'Таблица (2)';
        'Таблица (2)'[Num1] <> num1 
        && MONTH('Таблица (2)'[Date]) = MONTH(dateT)-1
        && 'Таблица (2)'[SiteID] = siteID
    )
)
var p2 = 
SWITCH(
    TRUE();
    ISBLANK(calcNum1); 0;
    NOT(ISBLANK(calcNum1)); 1
)
return p2

Это рабочий пример только с 1 колонкой "num1". Теперь ваша задача состоит в том, чтобы добавить другие столбцы в код, чтобы проверить значения