Show last two years of selected year

[LillyLegenda]

Good morning,

this is probably an hopfully an easy question but I cannot get it to work myself :(.

I would like the user to select one year on my PowerBi. 

Then I have a few graphs showing the one year but also some others showing the previous year.
I already investigated working with Sameperiodlastyear but did not really like the way it worked.

Therefore my idea was to add a column to my report that filters the years with DAX:

Year True = var YearSelection = SELECTEDVALUE('Date Table'[Year])

return if(YearSelection > OP_PO_Playbook[PO Year],1,0)

But this is not working for some reason. 

I disconnected the Date Table from the Data table. 

YearSelection is just for testing the variable.

Thank you very much.

[AlB]

@LillyLegenda 

What is not working exactly? "It stops working" is not particularly helpful

If you create a reationship the value in the slicer will of course filter the contents of the visual so you should only see the values selected in the slicer. Either do not create the relationship or use another unrelated table to select the base year

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[LillyLegenda]

Year True1 = var YearSelection = SELECTEDVALUE('Date Table'[Year])

return IF(Min(OP_PO_Playbook[PO Year])>YearSelection,0,1) 

This is working as a measure if the date table and the data table is not connected but it stops working when I connect them. But I do not understand why.

[AlB]

Hi @LillyLegenda 

Try:

Year True =
VAR YearSelection =
    SELECTEDVALUE ( 'Date Table'[Year] )
RETURN
    IF ( YearSelection > SELECTEDVALUE ( OP_PO_Playbook[PO Year] ), 1, 0 )

If this does not work please share the mock pbix you are using to show the visuals above 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[LillyLegenda]

I tested a few things and think I am rather close.

I added a new table (no connections) and used that for selecting the year. Then I used this DAX to filter on the last two years:

YearSmaller =

VAR YearSelection =

SELECTEDVALUE ( H_Year[YearNoConnection] )

RETURN

IF ( OR(

CALCULATE(YearSelection-0) = SELECTEDVALUE ( 'Date Table'[Year] ), --Change to -3 for showing 3 years

OR(CALCULATE(YearSelection-1) = SELECTEDVALUE ( 'Date Table'[Year] ),

CALCULATE(YearSelection-0) = SELECTEDVALUE ( 'Date Table'[Year] ))

)

, 1, 0 )

But when I filter the visual on "1" for YearSmaller it is not showing anything:

Does someone have an idea why? 🙂

[v-rzhou-msft · Accepted solution]

Hi @LillyLegenda 

If you build relationships between two tables, your OP_PO_Playbook will be filtered by your Year slicer.

So it won't show last two years' value due to the filter.

It is better for you to build a unrelated table to build a year slicer.

Then build a measure and add this measure into your filter on your matrix visual. Then set it to show items when value is 1.

I think your problem is that you now add your measure into filter and set it to show items when value is 1 then your visual won't show the result you want.

My Sample:

Reproduce your problem.

You can try to add PO Year into Rows and Business into Columns.

When I select 2021, it will show 2020 and 2019 in matrix visual.

My Measure:

Measure = 
var YearSelection = SELECTEDVALUE('Date Table'[Year])
return 
if(AND(MAX(OP_PO_Playbook[PO Year])<YearSelection,MAX(OP_PO_Playbook[PO Year])>=YearSelection-2),1,0)

You can download the pbix file from this link: Show last two years of selected year

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[HelmutKominik]

Hi Rico,

do you know why the solution does not work when you switch fields in Rows and Columns (Business in rows and PO Year in columns)? The calculation context is the same...

Leos

[LillyLegenda]

Thank you very much, this is working great 🙂

[LillyLegenda]

Sorry, @AlB  I did not see your message before.

This is working great if the Data Table is not connected to the Date Table. 

But if I connect both tables I get strange results like:

Do you also have a solution for this?

Thank you very much 🙂

[amitchandak]

@LillyLegenda , With date table sameperiodlastyear and all these combinations, should work

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Make sure that the date in your table does not have timestamp, refer: https://www.youtube.com/watch?v=OBf0rjpp5Hw

[LillyLegenda]

Thanks for you answer but as I said I would like to not use SamePeriodLastYear because I just want to use one Measure.

Do you know what might be missing in my Dax logic :)?

[amitchandak]

@LillyLegenda , You can not use selectedvalue in a new column. So can create a calculated column flag for the last 2 years.

example of new column

Year Type = Switch( True(),
year([Date])>= year(Today()) - 1 && year([Date])<= year(Today()), "This Year" ,
Format([Date],"YYYY")
)

[LillyLegenda]

Thanks but then I cannot click on for example 2019 to show 2019 and 2018.