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
Anonymous
Not applicable

Crazy Hard DAX Challenge for Veterans

Alright... here we go:

 

EDIT: All data is contained in one table. You can consider the combination of one day and one product ID to be a unique identifier.

 

My objective:

I need to write a DAX measure that shows the day-over-day change for a number only where the change is positive at the most granular level (the individual product)—but it needs to be able to evaluate at the day level of aggregation.

 

Calculating the day-over-day change is easy:

Available Qty Change = 
VAR AvailableQtyToday = CALCULATE(
    SUM('Finished Goods Inventory'[Available Quantity]),
    FILTER(ALL('Date'), 'Date'[Date] = MAX('Date'[Date]))
)
VAR AvailableQtyYesterday = CALCULATE(
    SUM('Finished Goods Inventory'[Available Quantity]),
    FILTER(ALL('Date'), 'Date'[Date] = MAX('Date'[Date]) -1)
)
RETURN 
AvailableQtyToday - AvailableQtyYesterday

Basically all this is doing is subtracting the sum of the available quantity on max date - 1 from the same thing on max date (i.e. today, if the slicer is relative).

 

This is where things get tricky.

 

At the most basic logical level, all I need to do is create a measure called "Positive Available Quantity Change". It seems, on the surface, like all I should have to do is this:

Positive Available Quantity Change =
CALCULATE(
    [Available Qty Change], 
    [Available Qty Change] > 0
)

Unfortunately, this provides no context in which Available Qty Change should calculate for the filter. Meaning, if I were showing this at the day level, where many products' available quantities were being summarized, if the entire day's value was negative, that day wouldn't show any data, even though there are actually individual items whose available quantity increased. And, specifically, I need it to calculate at the product level (i.e. the highest granularity). What I really need is something like this:

Positive Available Quantity Change =
CALCULATE(
    [Available Qty Change], 
    [Available Qty Change] (calculated at the row level) > 0
)

And in SQL, it would look like this:

WITH YESTERDAY AS(
SELECT 
    ProductID
    SUM(AvailableQty)
FROM
    FinishedGoodsInventory
WHERE 
    Date = GETDATE() - 1
    --Not quite the same functionality as MAX(Date), but you get the picture
),
TODAY AS(
SELECT 
    ProductID
    SUM(AvailableQty)
FROM
    FinishedGoodsInventory
WHERE 
    Date = GETDATE()
)

SELECT 
    SUM(A.AvailableQty) - SUM(B.AvilableQty) AS AvailableQtyChange
FROM TODAY A
JOIN YESTERDAY B ON A.ProductID = B.ProductID
GROUP BY
ProductID HAVING SUM(A.AvailableQty) - SUM(B.AvilableQty) > 0

So... I'm now throwing this to all of you geniuses. I've spent so much time pounding my head on the table that I'm losing all hope!

11 REPLIES 11
Anonymous
Not applicable

-- Dates must be a Date table in the model
-- connected to your fact table to the right
-- field.

[Unconditional Sum] = SUM ( 'Finished Goods Inventory'[Available Quantity] )

[_Positive Daily Change Helper] =
var __lastDate = lastdate ( Dates[Date] ) -- must be a table, hence lastdate
var __previousDay = previousday ( __lastDate )
var __delta =
	sumx(
		'Finished Goods Inventory'[Product ID],
		max(
			calculate (
				[Unconditional Sum],
				__lastDate
			) - calculate (
				[Unconditional Sum],
				-- You have to decide what to do when__lastDate is
				-- the first day in your calendar since there's
  -- is no previous day. __previousDay ), 0 ) return __delta [Sum of Positive Daily Changes] = var __output = sumx ( values ( Dates[Date] ), [_Positive Daily Change Helper] ) return __output

Best

Darek

Anonymous
Not applicable

@Anonymous 

A valiant effort, but it's definitely not working as intended. In virtually every instance, it just shows the available quantity. (I have verified that this is actually incorrect through desk checking.)

 

Annotation 2019-08-12 165742.png

Anonymous
Not applicable

Check out the latest try. If you still get the same number down the column it means you don't have the right relationships set up.

Best
Darek
Anonymous
Not applicable

[Unconditional Sum] = SUM ( 'Finished Goods Inventory'[Available Quantity] )

[_Positive Daily Change Helper] =
var __lastDate = lastdate ( Dates[Date] ) -- must be a table, hence lastdate
var __previousDay = previousday ( __lastDate )
var __delta =
	max(
		calculate (
			[Unconditional Sum],
			__lastDate
		) - calculate (
			[Unconditional Sum],
			-- You have to decide what to do when__lastDate is
			-- the first day in your calendar since there's
                            -- is no previous day.
			__previousDay 
		),
		0
	)
return
	__delta


[Sum of Positive Daily Changes] =
var __output =
	sumx (
		values ( Dates[Date] ),
		[_Positive Daily Change Helper]
	)
return
	__output

Try the above... You should create proper dimensions and not keep everything in one table - you are violating the correct design practices and hence you could face big problems down the line.

 

Best

Darek

 

Anonymous
Not applicable

@Anonymous 

I (think!) that last one worked! It looks like I would expect it to. I'll work on validating it and mark you correct tomorrow if it turns out you are. Also, I neglected to mention that our dates are in their own dimension, as are all of our other dimensions, but in this case, nothing outside of our fact table was necessary (aside from, well, the date... which I forgot to mention was outside of the table). My bad.

 

Thanks for the help, and I'll get back to you!

 

If you get a little spare time, would you mind explaining how this works? I'm right on the edge of understanding, but I'm a little foggy on the order of execution and the minutia of how everything plays together.

Anonymous
Not applicable

[Unconditional Sum] = SUM ( 'Finished Goods Inventory'[Available Quantity] )

[_Positive Daily Change Helper] =
var __lastDate = lastdate ( Dates[Date] ) -- must be a table, hence lastdate
var __previousDay = previousday ( __lastDate )

-- __delta calculates the difference in [Unconditional Sum]
-- between __lastDate and __previousDay but max makes sure
-- that when the difference is < 0, then 0 is returned.
-- Of course, [Unconditional Sum] respects all filters
-- that come into 'Finished Goods Inventory'. But when you
-- make the calculation you have to change context of the
-- date dimension. Hence CALCULATE.

var __delta =
	max(
		calculate (
			[Unconditional Sum],
			__lastDate
		) - calculate (
			[Unconditional Sum],
			-- You have to decide what to do when__lastDate is
			-- the first day in your calendar since there's
                            -- is no previous day.
			__previousDay 
		),
		0
	)
return
	__delta

-- Once you know how to calculate the positive change for
-- one day, you now sum up the changes for all the individual
-- days visible in the current context. Each measure is ALWAYS
-- wrapped up by the engine in CALCULATE, and since SUMX
-- is an iterator, context transition happens and the value
-- is calculate only for the currently iterated Date.

[Sum of Positive Daily Changes] =
var __output =
	sumx (
		values ( Dates[Date] ),
		[_Positive Daily Change Helper]
	)
return
	__output
Anonymous
Not applicable

First of all, CALCULATE needs a TABLE to be able to change context for the calculation of a measure. What you're trying to do is completely incorrect:

Positive Available Quantity Change =
CALCULATE(
[Available Qty Change],
[Available Qty Change] > 0
)

since [Available Qty Change] is a measure, not a column in a table.

Secondly, your SQL is incorrect as well. You, of course, cannot have HAVING without GROUP BY.

I'll try to show you the right solution shortly.

Best
Darek
Greg_Deckler
Super User
Super User

So, could you just do a SUMMARIZE with Product being the grouping and your existing measure being a column? Then just SUM up the column? Seems similar to a measure totals problem although kind of in reverse. These may help:

 

Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

 

Also, Matrix Measure Total Triple Threat Rock & Roll:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

 


@ 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...
Anonymous
Not applicable

Update: I've been toying with this, and I've come down to this measure:

Positive Available Qty Change = 
VAR __table = 
SUMMARIZE(
    'Finished Goods Inventory',
    'Finished Goods Inventory'[ProductKey],
    "__value",
    [Available Qty Change]
)
RETURN
IF(
    HASONEVALUE('Finished Goods Inventory'[ProductKey]), 
    IF([Available Qty Change] > 0, [Available Qty Change], 0),
    IF(SUMX(__table,[__value]) > 0, SUMX(__table,[__value]), 0)
)

It's giving me... not exactly what I'm looking for. I'm still digging.

Anonymous
Not applicable

Or try to substitute this in the latest solution:

 

[_Positive Daily Change Helper] =
var __lastDate = lastdate ( Dates[Date] ) -- must be a table, hence lastdate
var __previousDay = previousday ( __lastDate )
var __delta =
	max(
		calculate (
			[Unconditional Sum],
			__lastDate
		) - calculate (
			[Unconditional Sum],
			-- You have to decide what to do when__lastDate is
			-- the first day in your calendar since there's
                            -- is no previous day.
			__previousDay 
		),
		0
	)
return
	__delta

Best

Darek

Anonymous
Not applicable

A possibility... I'll dig into this and let you know if I make any headway.

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.

Top Solution Authors