I would like to create a measure that returns the result from a specific year. I have tried myriad combos and can't seem to get it to work. For example,
Thanks! That does return a result, just not the one I am expecting. I was hoping to return $7,580,493, but see $747,090. Please see my table below. Any thoughts?
first, The measure "Partial Year 1" is estimated for 2019 only so it will always return the same value. This need to be changed to return a result for each year in your table.
second, the value $7,580,493 is associated with 2020, so it will not be considered as we filter the calculation for only 2019.
If I understand well, you want to show the increments of cost by year under "Partial Year 1", if this is the case then the measure "Partial Year 1" needs to be changed to
Partial Year 1 =
var thisYear = selectedvalue('Date'[Year])
var prevYear = format(value(thisYear)-1,"0000")
var costThisYear = calculate([Incremental Cost by Year], 'Dates'[Year]=thisYear)
var costPrevYear = calculate([Incremental Cost by Year], 'Dates'[Year]=prevYear)
costThisYear - costPrevYear
Apologies, I had set the measure to filter on "2020". If I change it to "2019" so you can see I am having the same issue. I expect to see $7,288,000, however it is returning $,735,615 (see below).
Second point, yes I understand that all the values will be the same, that is my intention.
I am guessing there is some filtering issue related to the Calculate command. Any thoughts on how to return the result I am seeing in the origin measure?
I suspect that the data table contains other values for each year that are added to the measure, can you share a sample of your table.
As the filter to the left is using 2 different sources, the calculation will fail as one element (2Q-Self employed, 2019) is not selected.
This case will be difficult to find a suitable formula to calculate the needed value.
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!