Hi Experts,
I appreciate if you can shed some light on me
How can I show “ANSWER PRIOR YEAR” Column in the same table, BY PASSING THE CURRENTFILTER ON YEAR. I want the Current and Prior year to be dynamic. So with every new year’s data, I can only have the latest year compared to a year prior.
I have uploaded the image and workbook
I know VALUES isn’t correct to be used in my situation I am not sure what to use in “Red” for the expression in this calculate formula, i want to use allexcept to bypass the filter for year
ANSWER_Prior year = CALCULATE(values(Sheet1[ANSWER]),PARALLELPERIOD(Sheet1[Date],-1,YEAR),ALLEXCEPT(Sheet1,Sheet1[Question]))
Hereby a solution for in Power BI: (possibly there are other solutions)
Make a unique key with Power Query, “Add columns by Example” of “Org”, “small division” and “Question” together.
Make 2 new tables with DAX of current and last year (DAX does it automatically with every new upload)
Add a Lookup Formula in the Current Year table, searching for the Answer last year with the help of the Key, made in step 1.
Be aware that Lookup gives a error if no unique value can be found. This can happen if for the same key double lines of information or no data exists in LY , therefore “NA” or “To be Checked” should be added as alternative option, see formula used.
See the report and used formulas below and in attached PBIX below.
Ad 2 one of the two formulas used to create a new table:
TableCurrentyear =
var CY = RIGHT(YEAR( MAX(Sheet1[Date])),2)
return
CALCULATETABLE( Sheet1, Sheet1[FY] = “FY” & CY )
Ad 3 Formula used:
Answer LY = LOOKUPVALUE( Tablelastyear[ANSWER_CURRENT YEAR],
Tablelastyear[Key], TableCurrentyear[Key] ,“NA”)
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
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.
Thanks so much for another way to resolve this issue. I haven’t had exposure to using offset and will certainly use it more.
much appreciated for your prompt help!
