Calculate Sum by Product based on user selected year - 2 calculations: Sum before year and Sum After

[clarkbj71]

I am stuck on a problem:  I am trying to calculate the sum of each product by a filter (user selected) year.  The two calculations I need are the sum of each product before the selected year and the sum of each product after and including selected year.  I need to see this data in a stacked bar chart.  I am also looking to do the same with counts.  These sums need to recalculate depending on what year the user selects for the filter on the report page.   

[parry2k · Accepted solution]

@clarkbj71 try these measures:

Sum = SUM ( Table[Amount] )

Sum before selected year = 
VAR __selectedYear = SELECTEDVALUE ( SlicerTable[Year] )
RETURN
CALCULATE ( [Sum], Table[Year] < __selectedYear )


Sum after selected year = 
VAR __selectedYear = SELECTEDVALUE ( SlicerTable[Year] )
RETURN
CALCULATE ( [Sum], Table[Year] >= __selectedYear )

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[parry2k · Accepted solution]

@clarkbj71 better to do it in a title, make a big bold title. doing in a legend is not straightforward work. I'm planning to have a video on that part in the next few days as a workaround (subscribe to my YouTube channel, link below)

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[clarkbj71]

That is awesome!  I will need to play around with it a bit more to make sure but I think that worked.   It also seems like it will be easy to recreate this for the counts as well.  Question, Is there a way to create a dynamic way to  show year selected in the chart? i.e. Sum after "2012"?   I could also do this in the title bar but would need it somehere on the graph so it is easy for folks to see what year the chart is currently referencing.

[parry2k · Accepted solution]

@clarkbj71 try these measures:

Sum = SUM ( Table[Amount] )

Sum before selected year = 
VAR __selectedYear = SELECTEDVALUE ( SlicerTable[Year] )
RETURN
CALCULATE ( [Sum], Table[Year] < __selectedYear )


Sum after selected year = 
VAR __selectedYear = SELECTEDVALUE ( SlicerTable[Year] )
RETURN
CALCULATE ( [Sum], Table[Year] >= __selectedYear )

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.