Forum Discussion

JamesLeach's avatar
JamesLeach
Frequent Visitor
8 years ago
Solved

Change in Values over Time - Calculated Column?

I'll start out by saying I'm pretty new to Power BI and have clearly gotten in over my head with what I thought would be a relatively easy first 'real' use case...    This is a similar question to ...
Need Help
Tutorial Requests
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    Here is the result i got.  You may download my PBI file from here.

     

    Hope this helps.

     

  • JamesLeach's avatar
    JamesLeach
    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.  

JamesLeach's avatar
JamesLeach
Frequent Visitor
8 years ago

The solution I was trying does use the EARLIER function: 

 

ChangeAvailable =
Inventory[QtyAvailable]
    - CALCULATE (
        MAX ( Inventory[QtyAvailable] ),
        FILTER (
            ALL ( Inventory ),
            Inventory[Index]
                = EARLIER ( Inventory[Index] ) - 1
                && Inventory[SKU] = EARLIER ( Inventory[SKU] )
        )
    )

And I understand that I need to modify the filter.   One of the problems I ran into was that when I did so I ran into a circular-reference issue with the second calculated column.   I feel like I'm close, I'm just missing something.  

 

I'm gong to be spending some time this evening reading up on DAX and these functions - your article will be one of those resources.

 

Thank you,

James