Tricky Measure

[Anonymous]

I have a table with users and a YTD revenue goal number:

This table has a relationship established with a date table. There are gaps in the data in the above table so every user doesn't have data for every month of the year which makes it tricky when writing measures that involve dates. This measure is expected to go on a line graph.

I've been tasked with putting together a formula that will calculate the monthly % of their YTD goal they're expected to hit each month based upon:

So if the user has a yearly goal of $150,000 and is expected to complete 6.5% of that in January, that'd be $9,750. Then $8,250 for February, etc. until they hit 100% by the end of the year. How can I put this into a measure? I'm thinking it'd be a switch statement but I'm awful at writing measures that involve dates that have gaps 😀

[TomMartens]

Hey @Anonymous 

I have to admit that I do not understand your data model.

For this reason, please create a pbix with sample data, but still reflects your data model. Upload the pbix (andthe xlsx if you xlsx to create the sample data) to onedrive or dropbox and share the link.

How is the users/goal table related to the date table, which table holds the monthly percentages. Do these percentages change over time, meaning from year to year?

Regards,

Tom