topic Calculated column for getting difference between current and previous values in DAX Commands and Tips

Calculated column for getting difference between current and previous values

rubymaya — Wed, 06 May 2020 07:17:43 GMT

Hi experts,

I have issue with getting my calculated column to calculate the difference between current vs previous row/date

See the screenshot, what i want to achieve is that :

Difference between Qty for 6-Feb for Area PCBA_4C with 0-29 Days aging with the one in 5-Feb would be 41-4 = 37

ReportDate Area Aging Qty PrevValue Difference

5-Feb PCBA_4C 0-29 4 0 4

6-Feb PCBA_4C 0-29 41 4 37 ---> 41(currrent) - 4(previousrowday) *bear in mind it should not be date-1 date earlier might not be in the record

So far my dax to get the prev value isnot working 😞

I already assigned Index for each row but not working.

Appreciate any help

PrevValue = SUMX(

Fact_CUST_TB_WIP_BONEPILE_INOUT,

var myIndex = Fact_CUST_TB_WIP_BONEPILE_INOUT[Index]

var myRecord = Fact_CUST_TB_WIP_BONEPILE_INOUT[QTY]

var previousIndex =

CALCULATE(

max(Fact_CUST_TB_WIP_BONEPILE_INOUT[Index]),

FILTER(Fact_CUST_TB_WIP_BONEPILE_INOUT,Fact_CUST_TB_WIP_BONEPILE_INOUT[QTY] = myRecord && Fact_CUST_TB_WIP_BONEPILE_INOUT[Index] < myIndex )

)

var previousqty =

CALCULATE(

MAX( Fact_CUST_TB_WIP_BONEPILE_INOUT[QTY] ),

filter (

Fact_CUST_TB_WIP_BONEPILE_INOUT,

Fact_CUST_TB_WIP_BONEPILE_INOUT[Index] = previousIndex

&& Fact_CUST_TB_WIP_BONEPILE_INOUT[REPORT_DATE] = myRecord

)

return previousIndex

)

Re: Calculated column for getting difference between current and previous values

amitchandak — Wed, 06 May 2020 07:42:50 GMT

rubymaya , with a date table try calculation like these

Last Day Non Continous = CALCULATE(sum('order'[Qty]),filter(all(Table),Table[Date] =MAXX(FILTER(all(Table),Table[Date]<max(Table[Date])),Table[Date])))
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-1,Day))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Re: Calculated column for getting difference between current and previous values

rubymaya — Wed, 06 May 2020 08:38:47 GMT

Hi,

Did you mean, i have to create 2 New Columns?

I have error with the first block :

Would you mind using my tables as example :

Col1 = CALCULATE( sum(Fact_CUST_TB_WIP_BONEPILE_INOUT[QTY]),FILTER(

ALL(Fact_CUST_TB_WIP_BONEPILE_INOUT),Fact_CUST_TB_WIP_BONEPILE_INOUT[REPORT_DATE].[Date] = MAXX(FILTER(

all(Fact_CUST_TB_WIP_BONEPILE_INOUT), Fact_CUST_TB_WIP_BONEPILE_INOUT[REPORT_DATE].[Date] < MAX(Fact_CUST_TB_WIP_BONEPILE_INOUT[REPORT_DATE].[Date])))))

Re: Calculated column for getting difference between current and previous values

amitchandak — Wed, 06 May 2020 09:11:38 GMT

rubymaya , both are new measures

Re: Calculated column for getting difference between current and previous values

rubymaya — Wed, 06 May 2020 09:40:38 GMT

Ok, would you tell a little bit what it does, i still cant get the brackets right in the measure,...

Re: Calculated column for getting difference between current and previous values

rubymaya — Wed, 06 May 2020 09:58:46 GMT

Hi,

Ok i parse the error, but the value is not correct, it doesnt group by area and aging_day, see below screenshot

Re: Calculated column for getting difference between current and previous values

rubymaya — Wed, 06 May 2020 10:38:51 GMT

Hi Amit,

I cant find the pattern, but sometimes it works when i swap the fields in between, for eg : when i drag the qty, the measure disappear, when i remove the qty it appears back,... see 2 screenshots below appear, then disappear

i have relationship between fact table that we calculate and bridge 'table'

Re: Calculated column for getting difference between current and previous values

rubymaya — Wed, 06 May 2020 10:42:28 GMT

omg, im looking at the first column, for sure there will be no value coz it s the first one

Re: Calculated column for getting difference between current and previous values

rubymaya — Thu, 07 May 2020 01:07:39 GMT

Thx Amit, I think it;s for working now, would you mind to explain a little bit.

Re: Calculated column for getting difference between current and previous values

rubymaya — Thu, 14 May 2020 07:34:05 GMT

Hi Amit,

Is it possible to ignore Column Category from the pivot in terms of calculation, but still showing them on Pivot.

EG: If I show WW column, every Monday, it will not minus the qty from Sunday previous week. How do I address this because we still want to show the WW

Thx