cancel
Showing results 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

Anonymous
Not applicable

## measure that returns results from a specific year

Hello,

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,

Partial Year 1 =
CALCULATE(
[MyMeasure], (Dates[Date], YEAR(2019))

Partial Year = IF('Dates[Year] = "2019", [MyMeasure], 0)

I realize this should be fairly straight forward but just can't figure it out. Any thoughts?

Thanks much.

Robert
8 REPLIES 8
Resolver IV

Try this one

Partial Year 1 = Calculate([MyMeasure], 'Dates[Year] = "2019")

Anonymous
Not applicable

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?

Resolver IV

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)

return

costThisYear - costPrevYear

Anonymous
Not applicable

Thanks Yel.

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?

Thanks,

Resolver IV

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.

Anonymous
Not applicable

Thanks Yel!

Please find my data here.

Robert

Resolver IV

Ok thanks, will have a look and try to find the cause of incorrect value

Resolver IV

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.