Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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):
The end result would be a vizualisation that would look something like this:
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
Solved! Go to Solution.
Hi,
Here is the result i got. You may download my PBI file from here.
Hope this helps.
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
Hi,
Here is the result i got. You may download my PBI file from here.
Hope this helps.
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.
You are welcome.
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...
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |