Forum Discussion
Change in Values over Time - Calculated Column?
- 8 years ago
- 7 years ago
Okay, for anyone that comes across this thread in the future...
Removal of the 'index' column was a bad idea.
While it works without it, using the Date, as an index, memory consumption jumps through the roof. So much so that with the addition of a couple more weeks of data my PC couldn't refresh. And then, with a couple more weeks, the Power BI Service couldn't refresh.
Here is the DAX with the Index column:
QtyAvailableChange = IF ( ISBLANK ( LOOKUPVALUE ( Data[QtyAvailable], Data[Date], CALCULATE ( MAX ( Data[Date] ), FILTER ( Data, Data[Index] < EARLIER ( Data[Index] ) && Data[SKU] = EARLIER ( Data[SKU] ) && Data[Location] = EARLIER ( Data[Location] ) ) ), Data[SKU], Data[SKU], Data[Location], Data[Location] ) ), 0, [QtyAvailable] - LOOKUPVALUE ( Data[QtyAvailable], Data[Date], CALCULATE ( MAX ( Data[Date] ), FILTER ( Data, Data[Index] < EARLIER ( Data[Index] ) && Data[SKU] = EARLIER ( Data[SKU] ) && Data[Location] = EARLIER ( Data[Location] ) ) ), Data[SKU], Data[SKU], Data[Location], Data[Location] ) )Do it this way - not the way I previously referenced.
To create the index column I added Index as a custom column in Query Editor with the M query:
Index = Duration.Days(Date.From([Date])-#date(YYYY,MM,DD))
Where YYYY,MM,DD is the Year, Month and Day of my earliest Date record.
I have two tables first table is original table and second table is having same data but in the original table one value is modified that value is in second table and i dnt know in which coumn and in which table the value is modified please solve my problem