topic Re: DAX HELP in DAX Commands and Tips

DAX HELP

sarath_chandra — Thu, 15 Dec 2022 14:34:51 GMT

HI , in my dataset ,im having setup columns S1,S2,S3,S4,...upto S12.and date column - first day of every month (1/1/2018 to 12/1/2022) and field values . I need previous row difference of each rows .but first row of each setup should be null or zero (it should not take difference of previous set ups) for example s2 should not take the field values of s1. so that particular row should be zero or null. and in difference i have to use ABS dax function to avoid negative sign in output column.

kindly see the data set.please help me out. vry thankfull for your help

help me plzzzz

Re: DAX HELP

sarath_chandra — Thu, 15 Dec 2022 14:39:28 GMT

plz help Anonymous v-jianboli-msft .very thank ful

Re: DAX HELP

sarath_chandra — Thu, 15 Dec 2022 14:40:47 GMT

FreemanZ amitchandak tamerj1 plz help me out from this

Re: DAX HELP

FreemanZ — Thu, 15 Dec 2022 15:04:37 GMT

hi sarath_chandra

try to add a column with this:

Output2 =

VAR _setup = TableName[SETUP]

VAR _date = TableName[Date]

VAR _value =

MINX(

FILTER(

TableName,

TableName[SETUP]= _setup

&&VALUE(FORMAT(TableName[Date], "YYYYMM")) = VALUE(FORMAT(TableName[Date], "YYYYMM"))-1

TableName[Value]

)

RETURN

IF(TableName[Date]<>DATE(2018,1,1), ABS(TableName[OUTPUT] - _value),0)

I tried and it worked like this:

Re: DAX HELP

tamerj1 — Thu, 15 Dec 2022 16:28:51 GMT

Hi sarath_chandra

please try

Difference =
VAR CurrentDate = TableName[Date]
VAR CurrentValue = TableName[Value]
VAR CurrentSetupTable =
CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[Setup] ) )
VAR PreviousRecords =
FILTER ( CurrentSetupTable, TableName[Date] < CurrentDate )
VAR PreviousRecord =
TOPN ( 1, PreviousRecords, TableName[Date] )
VAR PreviousValue =
COALESCE ( MAXX ( PreviousRecord, TableName[Value] ), CurrentDate )
RETURN
ABS ( CurrentDate - PreviousValue )