Calculating value for specific quarterly goal within a table

[mdavis]

Hi all -

I am currently trying to calculate a quarterly sales goal for a new sales rep. I have the quarterly goals and already a measure calculating that t

Total Revenue:

QTR 1: $100,000

QTR 2: $200,000

QTR 3: $300,000

QTR 4: $400,000

Goals per Quarter:

QTR 1: $90,000

QTR 2: $250,000

QTR 3: $310,000

QTR 4: $450,000

Leadership wants me to calculate a column of a table that shows the % to goal by quarter but I can't figure out how to build the quarter filter into the formula. I have a date table with relationship to the value table. 

I've tried:

(IF(DATESBETWEEN('Date'[Date],DATE(2019,1,1), DATE(2019,31,3)), ([Total Revenue Amount/100000)),

IF(DATESBETWEEN('Date'[Date],DATE(2019,1,1), DATE(2019,31,3)),([Total Revenue Amount/250000)),

IF(DATESBETWEEN('Date Table'[Date],DATE(2019,7,1),DATE(2019,9,30)),([Total Revenue Amount]/310000)),

IF(DATESBETWEEN('Date Table'[Date],DATE(2019,10,1),DATE(2019,31,12)),([Total Revenue Amount ]/450,000)))

I've also tried using STARTOFQUARTER and ENDOFQUARTER, SWITCH(), and nested IF() statements but nothing works. 

Ideally it would look like the below, with each quarter calculating based on that specific quarter's revenue goal in the table. 

[Cmcmahan · Accepted solution]

So if you're using a table visual, this should be very easy.

Set the date to use a hierarchy instead of the straight value, and use only Year and Quarter.  Then add the [Total Revenue Amount] as a value, and a percentage calculation as your 3rd value.  

I'm not sure how you have the goal amounts stored in your model (either associated with each person or year/quarter or what), so I'm going to assume that you have each goal associated with a specific quarter and can add the percentage calculation measure yourself. 

If you need further help on how to set up goals within your data so that they correctly associate to a specific person/year/quarter, or how to create a date dimension, feel free to follow up here.