Calculating and visualizing sales goal over prior year's data

[Shelley]

Looking for help - I am trying to calculate a customer's goal, based on their sales from the prior year. Making matters worse, each customer's contracted start data is different. That is, one may begin January 1, another June 1, and still another December 1, and each wants to look at 12 months or four quarters of data, beginning with their contract start date. As such, we've created a generic calendar lookup table to create Quarter 1, 2, 3, 4 for each customer and Month 1, 2, 3 etc for each customer. Years -2 and -1 and 0 are all years prior to their first contracted date. The first year of their contract is Year 1. It seems like I am so close to figuring this out, but still have glitches.

Here's an example of the generic calendar we created:

Unfortunately, without real dates we are unable to utlize the date functions in Power BI.

Added column for each sales order record:

Test Champ Calendar Year = RELATED('Champ_Calendar_Lookup'[Champ_Calendar_Year])

Calculated Current Yr Sales Measure:

Champ CY Sales = CALCULATE(SUM(AnalyticsTable[Total_Revenue_Converted]), FILTER(AnalyticsTable,[Test Champ Calendar Year] = (MAX(AnalyticsTable[Test Champ Calendar Year]))))

So then I calculated Last Yr's Sales from which we will calculated the goal:

TestChampTRC US LY = CALCULATE(SUM(AnalyticsTable[Total_Revenue_Converted]), FILTER(AnalyticsTable,[Test Champ Calendar Year] = ((MAX(AnalyticsTable[Test Champ Calendar Year]))-1)))

Calculate quarterly goal (Last year's total sales by Champ * 1.2 / 4) - we want a straight line across the chart:

Test Champ Quarterly Goal = CALCULATE([TestChampTRC US LY]*1.2/4,ALL(Champ_Calendar_Lookup[Champ_Calendar_Quarter_Number]))

Two apparent issues still arise:

1. When I visualized it, the calcs are correct for some customers, but not others (it seems to be related to those customers with incomplete historical data. It almost seems like it is taking data from other years for the quarters that are missing data for last year). Why? Am I missing something in my formula?

2. This one appears correct; however, when I filter on year, the quarterly goal disappears. Why? I want to show one year at a time - sales in the bars, and goal as the straight line. Again, am I missing something?

[v-yuezhe-msft]

@Shelley,

Would you mind sharing me PBIX file or sample data of your tables?

Regards,

[Shelley]

I modified this formula:

Test Champ Quarterly Goal = CALCULATE([TestChampTRC US LY]*1.2/4,FILTER(ALL(Champ_Calendar_Lookup[Champ_Calendar_Quarter_Number]), (MAX(Champ_Calendar_Lookup[Champ_Calendar_Year])-1)))

and now, it seems to be calculating properly for every distributor.

I'm still having the issue of the goal disappearing when I filter. I'll have to see if there's an easy way to share a file with you as our data is confidential, so I will have to see if I can figure out a fast way to give you generic data. Thanks for your help.

[v-yuezhe-msft]

@Shelley,

You can upload your sample data/PBIX file to OneDrive and send me shared link of the file via Private message.

Regards,