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
7 years ago

Thank you very much, Ashish Mathur!  This was very helpful and informative.  

 

I did make a couple changes to the calculation.   The sign of the number seemed backwards to me so I changed the order of the calculation.    I also removed the dependance on the index column.   

 

Here is the modified DAX: 

 

QtyAvailableChange =
IF (
    ISBLANK (
        LOOKUPVALUE (
            Data[QtyAvailable],
            Data[Date], CALCULATE (
                MAX ( Data[Date] ),
                FILTER (
                    Data,
                    Data[Date] < EARLIER ( Data[Date] )
                        && 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[Date] < EARLIER ( Data[Date] )
                        && Data[SKU] = EARLIER ( Data[SKU] )
                        && Data[Location] = EARLIER ( Data[Location] )
                )
            ),
            Data[SKU], Data[SKU],
            Data[Location], Data[Location]
        )
)

 

I've experiemented with a vastly expanded dataset and I don't see any issues.

 

Thank you again for your help!   

 

Edited:  Changed DAX formatting and added clarification on the additional testing.

 

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

  • gwright15's avatar
    gwright15
    Helper I
    7 years ago
    Sorry for dragging up an old thread, but i can't seem to get the index column working.
    &nbsp;
    Index = Duration.Days(Date.From([Date])-#date(YYYY,MM,DD))
    I've receiving the error:
    &nbsp;Expression.Error: The name 'YYYY' wasn't recognized. Make sure it's spelled correctly.

    Edit - sorry me being daft - I now follow!