How to display Prior year value (text format) using Calculate and ALLEXCEPT

DAX DAX Calculations
3

@Maurice.Liu,

Welcome to the forum - great to have you here!

Questions like this one in Power BI likely would get six different answers from six different people – each of them valid. So, here’s a different approach than the one provided by @deltaselect, but that gets to the same final answer.

First thing in any time intelligence–related question I do is add Enterprise DNA Expert @Melissa’s awesome Extended Date Table to the data model and be sure to mark it as a date table.

I then created a concatenated ID of question number and small division, , and then used the incredibly powerful offset fields in the extended date table as the basis for the following measure:

Answer Prev Year = 
VAR SelID = SELECTEDVALUE( Data[ID] )
VAR SelFYOffset = SELECTEDVALUE( Dates[FiscalYearOffset] )
VAR LYFYOffset = SelFYOffset - 1

VAR Result =
CALCULATE(
    MAX( Data[ANSWER_CURRENT YEAR]),
    Dates[FiscalYearOffset] = LYFYOffset,
    Data[ID] = SelID,
    REMOVEFILTERS( Data ),
    REMOVEFILTERS( Dates )
)

RETURN Result

image

If you are not familiar with the offset approach, I recommend the following video. This approach can be used to simply solve.a myriad of common and sometimes complex time intelligence problems.

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

2 Likes