Today vs Yesterday

DAX DAX Calculations
1

Hi,

I am creating a POC for a mobile report which shows movements in the HRC (Steel) commodity price. I have a simple table of daily prices and would like to present cards showing today’s price, yesterdays price and the change between the 2.

I have created a calculated column called WhatDayIsIt:

“WhatDayIsIt =
VAR today = TODAY()
VAR rowdate = CALCULATE(SUM(‘DataTable’[Date]))
RETURN
IF(DATEDIFF(today,rowdate,DAY)=0,“Today”,
IF(DATEDIFF(today,rowdate,DAY)=-1,“Yesterday”,
“History”))”

I’m then trying to create a measure that gives the change between the HRC price for Today and Yesterday.

“T/Y Delta = SUM(‘DataTable’[Asia HRC])-CALCULATE(SUM(‘DataTable’[Asia HRC]),PREVIOUSDAY(‘DataTable’[Date]))”

This works, but when i apply the WhatDayIsIt filter, the dates that i require a difference for is incorrect. I’m sure it has something to do with context, but i can’t crack it.

Any help would be greatly appreciated.
Regards,
Bronwyn

2

@bgood,

When it comes to time intelligence in DAX, there are always a ton of different ways to accomplish the same thing. Here’s just one approach via 3 separate measures:

Today's Price = 

CALCULATE(
    SELECTEDVALUE( 'HRC Data'[Price] ),
    Dates[Date] = TODAY()
)

Yesterday’s Price =

CALCULATE(
    SELECTEDVALUE( 'HRC Data'[Price] ),
    Dates[Date] = TODAY() - 1
)

Price Diff =
[Today’s Price] - [Yesterday’s Price]

image
image.png715×370 12.5 KB

I hope this is helpful. Full solution file posted below.

3

Thank you Brian, that is very helpful, and worked.
Are you able to tell me what i could use to work out the most recent date available in the table vs the second most recent date available in the table? For example, if today’s data has not yet been updated/populated in the table ‘today’s price’ would be yesterday’s price and ‘yesterday’s price’ would be the day before yesterday?

4

@bgood,

Sure - two more measures:

Most Recent Data Date = 

CALCULATE(
    MAX( 'HRC Data'[Date] ),
    'HRC Data'[Price] <> BLANK()
) 

Next Most Recent Data Date = 

[Most Recent Data Date] - 1

image

You can then adapt the measures in the prior solution to set the filter conditions to [Most Recent Data Date] and [Next Most Recent Data Date], instead of TODAY() and TODAY() - 1 respectively.

5

@BrianJ - if i put these measures into a table vis, should every row have the same date? This is not the case in my example.

6

@bgood,

Nice catch. Sorry , my bad - needed to remove the filter on the HRC Data[Date] field for the measure below to work properly in the table context:

Most Recent Data Date = 

CALCULATE(
    MAX( 'HRC Data'[Date] ),
    'HRC Data'[Price] <> BLANK(),
    REMOVEFILTERS( 'HRC Data'[Date] )
)

Should work properly now…:face_with_head_bandage:

image

Updated solution file posted below.

7

@BrianJ sorry for the late reply. Our organisation has recently updated to on premise version May 2019, and the REMOVEFILTERS formula doesn’t seem to be available :frowning:

Would this be the only way to do this? I tried: KEEPFILTERS(ALLEXCEPT(Daily_FX,Daily_FX[Date]), but this didn’t seem to work.

8

@bgood,

Anywhere you see REMOVEFILTERS, you can substitute ALL and get the exact same results (though the converse is not true). I use REMOVEFILTERS where applicable because I think it’s more descriptive of what’s going on, but you can get along perfectly without it.

If you’re interested, here’s a good article on when you can and can’t use REMOVEFILTERS as a substitute for ALL:

  • Brian
9

Excellent, ALL worked as expected. Thank you @BrianJ