Forum Discussion
summing previous year while allowing current year filter power bi
Hello, I'm trying to display current year gained from last years totals.
Table appears as followed:
Type Type2 CurrentYrGained 2021Growth 2020Gained(Not displayed but used for calculation)
A 1 100 100% 50
A 2 50 10% 45
B 1 200 0% 200
B 2 60 0% 60
Slicer = This Year
Gained Calulation = (CurrentYrGained/2020Gained)-1
Question:
How can I display only the 2021 growth is visual (Slicer = current year) while allowing the 2020Gained within the gained calculation?
Hi Anonymous ,
Put the Year of Date hierarchy in the slicer and select current year, it should work.
Attached a sample file in the below, hopes it could help.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Anonymous , is this your source table ?
If you have date use Time intelligence with Date table or use a separate year table
// With date and Date table
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"))// With year and separate year table
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))diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])Default current year
https://www.youtube.com/watch?v=hfn05preQYA
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_0uATo 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 :radacad sqlbi My Video Series Appreciate your Kudos.
I cant lock the report to only this year and last year. I will need to allow users to be able to adjust the Date Slicer without impacting the calculation for the previous year.
Below was my custom measure attempt:
My Measure=
test1 = SUM('Engagement'[Engagement Gains])
var test =
CALCULATE('Engagement'[test1],SAMEPERIODLASTYEAR('Engagement Data'[Date].[Date]),ALLEXCEPT('Engagement Data','Engagement Data'[Type],'Engagement Data'[Type2]))
return
(sum('Engagement Data'[Engagement Gains])/test) -1What I expect/want to see:
Year:
2021
Type Type2 Growth
A 1 50%
Slicer = Current year
What I'm getting:
Year:
2020 2021
Type Type2 Growth Type Type2 Growth
A 1 Infinity A 1 50%
Slicer = Current yearConclusion: I need Slicer of Current year to work by removing the year 2020 from report and only showing year 2021
Hi Anonymous ,
Put the Year of Date hierarchy in the slicer and select current year, it should work.
Attached a sample file in the below, hopes it could help.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
