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.
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.
Index = Duration.Days(Date.From([Date])-#date(YYYY,MM,DD))
I've receiving the error:
Expression.Error: The name 'YYYY' wasn't recognized. Make sure it's spelled correctly.
Edit - sorry me being daft - I now follow!