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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
yelsherif
Resolver IV
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? 

 

Year only.PNG

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, 

 

Partial Year 1.PNG

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 

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.