Difficulty in comparing same measure but different values

[RenanPNog]

Hey, guys! First of all, i'm a super newbie in PowerBi.

I work at a company that sells courses, we have 12 courses and each one of them have different time durations to "finish a campaign".

For example, i have a Data Base where i see the Course X's progress and how many students have enrolled untill now, each line of this data base (i'll keep only courseX for example) is 1 day, and a progress percentage. 

So what i have is:

line 1 --- Course X --- Campaign progress (%) --- Enrollments

In short, that's the data i do have. What i need to do, is check how Course X performed in the last year compared to now, but the problem is, last years percentages wasn't same. While course X's percentages in 2022 are (0; 0,5; 1; 1,4; 2.....), course X's percentages in 2021 were (0; 0,3; 0,7; 1,2; 1,6.....)

It is not worth to round the percentages numbers, since each percentage is 1 day and i want to see same progress in both campaigns.

I've done this in Excel, but in that tool i have freedom to lock on cells and call them individually, and i can't do same in PowerBI.

Last but not least, when i'm looking at both campaigns, i need to always get the old campaign a higher percentage, so i'm not oversizing my actual numbers. For example, 2022 Course X is 35,4% with 300 enrolls; In 2021 i have Course X 35,2% with 200 enrolls and 35,9% with 210 enrolls, in this case, i would need to compare 2022 - 35,4% with 2021 - 35,9% (i shouldn't compare my actual campaign with a "smaller" % value)

[RenanPNog]

Hello Ibendin,

Any news on this?
Do you need any further info.....? 

[lbendlin]

see if this works for you

[RenanPNog]

Hello Ibendlin,

At first it seemed that the solution fit what we need... then I noticed you simply removed the 'course_percentage' column, which is the most important part of the way we need to calculate the numbers, in order to display the data the most accuretly as possible, given the circumstances.
May I ask why?
And what's the thought process behind it....?

Thanks in advance.

[lbendlin]

Please provide a separate table with course | campaign | Start date | End Date

[RenanPNog]

Hello Ibendin,

I included the start dates (corresponds to 0 in course percentage) in the dummy.
Please remember that (I believe, please correct me if I'm wrong) another search for values will be necessary for that case, as we'll have to look for 0 in couse percentage in order to retrieve the courses start dates (each course's campaign starts at a different dates).

[RenanPNog]

Hello again,

There you go - I kept only 2 courses for the sake of brevity, and added some data around the same dates for 2021 and 2022.

[lbendlin]

Please help me understand what I am looking at

for course 1 and 2021 we ended up with 41 enrollments and 6.9% . What is 6.9 the percentage of?  What value represents 100 % ?  The amount of slots overall for the training?

Are you saying you want to normalize the 6.9% of 2021 against the 4.37% of 2022?   A weighted comparison?

[RenanPNog]

Hello,

Here are the answers, I hope I was able to clarify them!

"for course 1 and 2021 we ended up with 41 enrollments and 6.9% . What is 6.9 the percentage of? What value represents 100 % ? The amount of slots overall for the training?"

So the percentage is the evolution of the course, and it's related to the date; and each 3 days is around 1% from the total. For ex, here we see that the Course 1 started at 3%, which means the campaign started really at 08/01/21 (or earlier):

 While for this year, the campaign started even earlier for the same course (around the end of July):

So, considering that each campaign lasts around 6 months, we can say the 100% ends at 02/01/2023 ( in this example, usually campaigns happen within a specific year.

As for the slots, there are no limits for enrollments, so the percentage is really the evolution of that course in a given campaign over time.

Are you saying you want to normalize the 6.9% of 2021 against the 4.37% of 2022?   A weighted comparison?

Context: Our baseline for metrics calculation is the previous campaign, so we always look at the past in order try to measure the current campaign; since the courses have different duration times (it's possible to see that with the dummy), the way we found in order to compare is through the percentage. 

Our goal is to compare how we are today (percentage of current campaign (2022)) to with how we were in the last campaign ( percentage of previous campaign (2021)), but since they're different we need to find the most approximated value - so with that we can see the running totals we had until that point in time (2021) and then we'll have an idea if this year's performance is good or bad, and take appropriate action accordingly. 
I'm not sure if this would be a weighted comparison, it's more like a approximated value comparison to get another value, that change with time.

Let me know if you have any further questions - I reckon this is a bit complex!!
Thanks in advance for your support!

[lbendlin]

It would be much easier if the course start and end dates were available for each campaign.

[lbendlin]

link says "access denied"

[RenanPNog]

Hello, 
Here, I fixed it:
Dummy 
I suggest downloading it, as it has a reader permission.

[lbendlin]

Your sample data only has 2022

[RenanPNog]

Hello, sorry for the delay.

I made a sample file with dummy values which show exactly what's needed, just click the link.
We're having much trouble 'translating' it into PBI 'cause we couldn't find a way to filter the table to pick specific values from the columns by comparing them, and since the DAX functions iterate in rows, it's kind of hard to see the logic behind it. Also, we had trouble working with date/time in PBI, by using the date today to compare with another one in the past.....but since we use a different value for comparison, I'm not sure how much of a trouble it can be for the final solution.
The dummy has the results we want, but here's another snipped from the desired result:

If there's anyway to replicate exactly what's in the spreadsheet in PBI we'll be able to perform the calculations we need - thanks in advance for your assistance. If you need any further info, please do let me know. 

[lbendlin]

Please provide sanitized sample data that fully covers your issue. I cannot help you without usable sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[RenanPNog]

Sure, sorry about that. 

- Here's an example of my main data base:

In this case, i'm getting only data for "course1", but there are data for another 11 courses as well.

i know that today (august 6th for example), my course 1's progress is "3,95%", i need to get the "exact match or next larger item", in this case would be "4,1%" 

- Here's the sheet that i need to have at the end:

But these values are going to change untill the end of my "campaign", 'cause everyday is a new % progress for each course.

After i have this final sheet, i can check how many enrollmens i had at that moment in time, and compare both campaigns equally.

Hope this makes a better understanding, let me know if it's clearer now.

Thank you!

[lbendlin]

Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.