Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JamesLeach
Frequent Visitor

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 this post from last May.

 

My use case is a bit different, however, because of my data.   The question, above has a single instance of the product per day where I have multiple (because of multiple locations).  Additionally, I have multiple quantity columns (called 'values' in the question, above).   

 

My data, basically, looks like this:

SampleData.PNG

 

I want to create a calculated column for each of my Quantity columns so that the table would look like this (although the blank cells at the top should really be zero, not blank - but that's easy enough to fix using 'change value', I believe): 

SampleWithChange.PNG

 

The end result would be a vizualisation that would look something like this: 

EndResult.PNG

 

When I tried to use the calculated column from that post the change is calculated against the previous instance of the QuantityAvailable (for example) and not the matching value based on location.  I've spent a fair amount of time trying to modify the filter to make it work but I'm not having any luck so far. 

 

Does anyone have any suggestions or assistance?    Is there an easier way?  A trick with an existing vizualization I'm missing?  

 

Thanks,

James

2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

Hi,

 

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

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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.  

View solution in original post

8 REPLIES 8
saikiran
Helper I
Helper I

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

Ashish_Mathur
Super User
Super User

Hi,

 

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

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

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.  

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!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

The key to this is to use the EARLIER function. You just need an extra FILTER statement that it needs to also match your "EARLIER" location as well.

 

See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.