I need a measure to account for different date ranges

[Anonymous]

I have the attached formula that I'm using to calculate the number of rows against a goal.  I'm using the Contacts Made Counter is if statements that produce a 1 for each row that meets the criteria.  These are surveys so each row has a date.   So I'm summing the number of contacts made, and want to divide that by a goal for a percentage, in this case 7.   

CFTSS Quarterly % to Goal = DIVIDE(SUM('Community Programs Quarterly Survey Data'[Contact Made Counter]),7)

My problem is that the goal changes and has changed.  So i would like to be able to say the goal was 6 between some start date, and some end date, and is now 7 from some date going forward, until it changes again then I can update the formula.

Can anyone help me?

[v-lionel-msft]

Hi @Anonymous ,

You can create a 'What If' parameter.

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Lionel,

That would work, however, i'm hoping to ulimately be able to graph over time the % achievement rate relative to goal.  So i'm hoping to be able to let a single measure account for the changing goal over time?  Got any ideas how to do that?

[v-lionel-msft]

Hi @Anonymous ,

Or this?

Measure = 
DIVIDE(
    SUM('Table'[Contact Made Counter] ), 
    CALCULATE(
        COUNTROWS( 'Table' ),
        FILTER(
            ALL('Table'),
            'Table'[Column] = "Yes"
        )
    )
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[Anonymous]

Here's the output in table form.  I can't figure out how to insert files so i'm just screen shotting the data as it comes in from excel csv.