correct syntax for lastnonblank in measure for year to year value

[pat_energetics]

Hi,

I have a fact table with multiple column values referenced to a date 1/1/20XX in the row.

The 1/1/20XX date within the fact table has a many to one relationship to a date key in the calendar table and so correctly retrieves the value on 1/1/20XX, but I want the same value returned for all dates in that year when calculating measures where data is available from other fact tables for all unique dates within that year.

Measures on the other fact table/s with values for every date within the year are correctly retrieving values - so I don't think there is any issue with the fact table or the relationships to the calendar table....

It appears that the lastnonblank function is the one to use, but I can't seem to get the right syntax in the measure (I'm still returning blank for all dates other than 1/1/20XX). 

Are there any other examples where a value referenced to a single date can be applied to all dates within the year using this function, or do I need to rebuild the model and create a relationship to a new (unique year only) table and cascade the relationships from year table -> calendar date table ???

Thanks in advance

[Greg_Deckler]

Difficult, if not impossible to say without some sample data, but it sounds like it sounds a lot like TOTALYTD:

https://msdn.microsoft.com/en-us/library/ee634400.aspx

But without some sample data and what your measure calculation looks like, difficult if not impossible to say.

As for LASTNONBLANK, very poorly documented and no examples, I haven't quite figured that function out.

[pat_energetics]

Thanks, and apologies I should have made the question clearer with sample data but I have to be careful to make the data generic enough to avoid disclosure of confidential info..

Yes agree - Total YTD may work if there is only one value for OpCap_MW at one date within the year (1/1/20XX) and the fact table is not updated with new values at later dates within the year ??? I'm thinking using YTD would calculate the sum of the OpCap_MW values and may not produce correct results downstream if the fact table is updated with a new value at a later date (or has multiple rows for different scenarios as this fact table does).

The fact table StID_Volume has columns YearDate (currently always 1/1/20XX), StID (a station identifier), SupplyScenario and OpCap_MW (amongst others) 

Here is the underlying measure that is used for downstream calculation. This measure currently only returns a value on 1/1/20xx and not all dates from 1/1/20xx to 31/12/20xx as desired.

StID_Op_Cap =
VAR SupplyScenario =
IF (
HASONEVALUE ( SupplyScenarioTable[SupplyScenario] ),
VALUES ( SupplyScenarioTable[SupplyScenario] ),
"SupplyMid"
)

//Checks to ensure a default to the MID case scenario if nothing selected on slicer or filter //

VAR CapacityThreshold = 5

//We don't consider stations with capacity lower than 5MW //
RETURN

CALCULATE (
( MAX ( StID_Volume[OpCap_MW] ) ),
StID_Volume[SupplyScenario] = SupplyScenario,
StID_Volume[OpCap_MW] >= CapacityThreshold,
StID_Volume[ScenarioOpValue] = "Yes")

[Greg_Deckler]

@pat_energetics - So, am I understanding you to say that let's say that the MAX(StID_Volume[OpCap_MW]) for 2016 with your filters is 100 and that happened on February 16th, 2016. Are you saying that you want 100 displayed for every date in 2016?

Sorry, trying to wrap my head around what you are trying to accomplish.

[pat_energetics]

Yes - that would be correct - 100 for all dates in 2016 would be an acceptable outcome.

[Greg_Deckler · Accepted solution]

OK, try adding an ALLEXCEPT clause to your CALCULATE for an assumed "Year" column:

CALCULATE (
( MAX ( StID_Volume[OpCap_MW] ) ),
ALLEXCEPT([Year]),
StID_Volume[SupplyScenario] = SupplyScenario,
StID_Volume[OpCap_MW] >= CapacityThreshold,
StID_Volume[ScenarioOpValue] = "Yes")

Basically, expand remove all context filters other than your "Year" and then add in your additional context filters. Note that you need the rest of your code, this is just replacing the CALCULATE portion of your code.