topic create a column that is the difference of adjacent variables. in DAX Commands and Tips

create a column that is the difference of adjacent variables.

Anonymous — Mon, 30 Jan 2023 10:54:04 GMT

I want to create a column that is the difference of adjacent variables.

Ex. Q1 = Sup_group 2-2 = 0 (Sup_group of Q1 - Sup_group of Q1)

Q2 = Sup_group 2-3 = -1 (Sup_group of Q1 - Sup_group of Q2)

Q3 = Sup_group 3-4 = -1 (Sup_group of Q2 - Sup_group of Q3)

Q4 = Sup_group 4-2 = -1 (Sup_group of Q3 - Sup_group of Q4)

result example.....

sample data....

help me please 😂

thank you,

Re: create a column that is the difference of adjacent variables.

tamerj1 — Mon, 30 Jan 2023 11:08:59 GMT

Hi Anonymous
Please refer to attached sample file with the solution

Result = VAR CurrentQuarter = 'Table'[Quarter] VAR CurrentSupGroup = 'Table'[Sup_group] VAR GroupProvinceTable = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[group], 'Table'[province] ) ) VAR TableBefore = FILTER ( GroupProvinceTable, 'Table'[Quarter] < CurrentQuarter ) VAR PreviousRecord = TOPN ( 1, TableBefore, 'Table'[Quarter] ) VAR PreviousSupGroup = COALESCE ( MAXX ( PreviousRecord, 'Table'[Sup_group] ), CurrentSupGroup ) RETURN PreviousSupGroup - CurrentSupGroup

Re: create a column that is the difference of adjacent variables.

Anonymous — Tue, 31 Jan 2023 03:02:27 GMT

Hi tamerj1 ,

Thank you for the DAX. It helped me to pass well. 🙏🙏🙏