Current Year and Current Year +1 and Current Year + 2

[ramshoney1225]

I have a year slicer with values which is a single selection value (2018,2019,2020,2021,2022)

I have three reports named by current year(2018), current year + 1 (2019) and current year + 2 (2020)

Each of the 3report consisits of 3 columns

Current Year             Year, Financialstatus, Revenue
Current Year + 1       Year, Financialstatus, Revenue
Current Year + 2      Year, Financialstatus, Revenue 

and when i select year 2018, current year needs to show 2018 data,

current year +1 needs to show 2019 data

current year + 2 needs to show 2020 data

Any help on this would be highly appreciated.

Thanks,

Rams

[Icey]

Hi @ramshoney1225 ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards,

Icey

[Icey · Accepted solution]

Hi @ramshoney1225 ,

I have three reports named by current year(2018), current year + 1 (2019) and current year + 2 (2020)

---

What do the "three reports" mentioned mean? You create three .pbix files? Or three pages in one .pbix file? Or just three visuals on one page?

For three visuals on one page, create a measure like so:

Measure =
SWITCH (
    TRUE (),
    MAX ( 'Table'[Year] ) = SELECTEDVALUE ( YearSlicer[Value] ), 1,
    MAX ( 'Table'[Year] )
        = SELECTEDVALUE ( YearSlicer[Value] ) + 1, 2,
    MAX ( 'Table'[Year] )
        = SELECTEDVALUE ( YearSlicer[Value] ) + 2, 3
)

Then, put it into Filters on this page of each visual.

Then, you will get this:

For three pages, it is needed to sync the slicer.

For three .pbix files, it is not supported to filter among .pbix files.

BTW, .pbix file attached.

Best Regards,

Icey

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

[amitchandak]

@ramshoney1225 , you can use time intelligence with date calendar

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"))
next year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],1,Year)),"12/31"))
next to next year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],2,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))
next Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])+1))
next to next  Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])+2))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.