Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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?

1 ACCEPTED SOLUTION

Hi @Anonymous ,

Put the Year of Date hierarchy in the slicer and select current year, it should work.

slicer.png

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.

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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_0uA

 

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 :radacad sqlbi My Video Series Appreciate your Kudos.

Anonymous
Not applicable

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) -1

What 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 year

Conclusion: 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.

slicer.png

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.

Anonymous
Not applicable

This will work. Its does not allow for "Relative Dates" but, still functions by displaying single year while still doing previous year calcualtion. Also to avoid the infinity return for years that do not have a prior year I've update dax in measure with below.

 

Thank you for you aid. 

 

 

Measure =
VAR test =
CALCULATE (
SUM ( Engagement[Engagement Gains] ),
SAMEPERIODLASTYEAR ( 'Engagement'[Date].[Date] ),
ALLEXCEPT ( 'Engagement', 'Engagement'[Type], 'Engagement'[Type2] )
)
RETURN
IF(test=BLANK()||test<0, 0,
( SUM ( Engagement[Engagement Gains] ) / test ) - 1)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors