MAX Prior Period based on LastDate return value

[Anonymous]

Hi,

I have two measures which return the price based on the max date and both are working correctly.  What I am looking to do is similar to DateAdd but I don't believe DateAdd can be combined with MAX/MAXX.

I've tried to filter using as I have a date dimension but it's returning a list of values instead of a scalar value :FILTER(ALL('date table'[Date ]);'date table'[Date ]<=DATE(YEAR(MAX('date table'[Date ]))-1;MONTH(MAX('date table'[Date ]));DAY(MAX('date table'[Date ])))) 

Measure 2 =

MAXX (

TOPN (

1,

FILTER ( 'Benchmarks_PowerBI', 'Benchmarks_PowerBI'[ Price ] <> BLANK ()),

Benchmarks_PowerBI[Date], DESC

),

Benchmarks_PowerBI[ Price ]

)

Measure 5 =

var _max = CALCULATE(

MAX(DimDate[Date]),

'Benchmarks_PowerBI','Benchmarks_PowerBI'[ Price ]<> BLANK(),

ALL ( Benchmarks_PowerBI ))

return

SUMX(FILTER(Benchmarks_PowerBI, Benchmarks_PowerBI[Date]=_max),Benchmarks_PowerBI[ Price ])

Ultimately, I'm looking for something like this:

Measure 2 = 
**Current Period**VAR __max = MAX('Table5'[Fiscal Year])
**Prior Period**VAR __min = MIN('Table5'[Fiscal Year])
RETURN
SUMX(FILTER('Table5',[Fiscal Year]=__min),[Rate]) - SUMX(FILTER('Table5',[Fiscal Year]=__max),[Rate])

Respectfully,

Robert

[Anonymous]

Hi.

If anyone could understand what you really need, then maybe someone would be able to help you...

Best

Darek

[Anonymous]

Hi Darek,

There is a fact table, benchmarks, a dimbenchmark, and a dimdate table in the model.  Essentially, we are looking to create 1,3, 5 year comparisons based on the current context i.e. year is 2019.  For 2019, we would want to return the most recent date with a price for a ticker.  Then, we would want to shift the context to grab the most recent price from the previous year.  For example, we would be comparing the closing price of "A" on 12-31-2018 to the closing price of the most recent day with a price.  Essentially, we are looking to a YTD comparision.  We would be looking to create the same mesure for QTD and 1-3-5 year from the date of the current price.

Respectfully,

Robert

[Anonymous]

OK. Would you please obtain a shareable link to an example file and post it? I wanna see your model and data. You can get a link to a file if you put the file on OneDrive, GoogleDrive, Dropbox... or some other cloud storage service. if you do that, please do not forget to make the link accessible to anyone (you have to set permissions right).

Thanks.

Best

Darek

[Anonymous]

Please let me know if this link does not work:

https://onedrive.live.com/?authkey=%21ANQYX5UCHJaHyuU&id=E1D90AB70FBCB9DF%21805&cid=E1D90AB70FBCB9DF

You'll be able to see several examples of how it does not work.

Respectfully,

Robert

[Anonymous · Accepted solution]

OK. Here's a solution:

You have to always know what you're slicing by. You always should slice by the attributes of dimensions, not those of the fact table. The above works because Year comes from DimDates, not your fact table. I'd suggest you hide all the attributes of the fact table save the measures.

Here are the measures you see above:

Darek - Most Recent Date = 
var __onlyOneTickerVisible = HASONEFILTER( DimBenchmark[Benchmark Ticker] )
var __result = MAX( Benchmarks_PowerBI[Date] )
return
    if( __onlyOneTickerVisible, __result )

Darek - Most Recent Date Shifted = 
var __onlyOneTickerVisible = HASONEFILTER( DimBenchmark[Benchmark Ticker] )
var __oneShiftVisible = HASONEFILTER( 'Shift From Base Year'[Shift in Years] )
var __numOfYearsBack = SELECTEDVALUE( 'Shift From Base Year'[Shift] )
var __result =
    CALCULATE(
        [Darek - Most Recent Date],
        DATEADD( DimDate[Date], -__numOfYearsBack, YEAR )
    )
return
    if( __onlyOneTickerVisible && __oneShiftVisible, __result )

Darek - Most Recent Price = 
var __onlyOneTickerVisible = HASONEFILTER( DimBenchmark[Benchmark Ticker] )
-- In the current date context get the latest price visible
var __result = LASTNONBLANK( Benchmarks_PowerBI[ Price ], Benchmarks_PowerBI[ Price ] )
return
    if( __onlyOneTickerVisible, __result )

Darek - Most Recent Price Shifted = 
var __onlyOneTickerVisible = HASONEFILTER( DimBenchmark[Benchmark Ticker] )
var __oneShiftVisible = HASONEFILTER( 'Shift From Base Year'[Shift in Years] )
var __shiftInYears = SELECTEDVALUE( 'Shift From Base Year'[Shift] )
var __price =
    CALCULATE(
        [Darek - Most Recent Price],
        DATEADD( DimDate[Date], -__shiftInYears, YEAR )
    )
return
    if( __onlyOneTickerVisible && __oneShiftVisible, __price )

All the other measures can be deleted from your file.

Now, here's what you have to do:

1. Mark your DimDate table as DATE TABLE.
2. Create a table that will hold shifts.

1. Here's how you mark a table as date table. Go to Modeling  > Calendars and while your DimTable is displayed in the table view mode, click on Mark as Date Table.

2. Create a table like this:

Hide Shift (it's for calculation only) and leave the other one to be used on a slicer. Do not connect the table to anything else. It's a parameter table.

Never, ever select dates from your fact table. Only from your Date dimension. Then everything will work.

By the way, if you put quarters (from your Date table) in the slicer for Years, it should also work. Basically, for any selection of attribute in the DimDate dimension, it should work. Of course, on condition that I've understood the logic correctly. But you've got a good start, so you might want to adjust it to your needs if it acts up 🙂

Best

Darek

[Anonymous]

Hi Darek,

I sincerely apprecaite your time and explanation, it will not only be helpful to myself but others whom are searching the boards for a solution as well.

Respectfully,

[Anonymous]

There's a mistake in one of the formulas...

Please change it to this:

Darek - Most Recent Price = 
var __onlyOneTickerVisible = HASONEFILTER( DimBenchmark[Benchmark Ticker] )
var __mostRecentDate = [Darek - Most Recent Date]
var __result =
    CALCULATE(
        VALUES( Benchmarks_PowerBI[ Price ] ),
        KEEPFILTERS( DimDate[Date] = __mostRecentDate )
    )
return
-- In the current date context get the latest price visible
    if( __onlyOneTickerVisible, __result )

Sorry. We can't use LASTNONBLANK in this case.

Best

Darek