How to compare running totals by using evolution in percentage?

[talitanieps]

Hello everybody!! I'm super newbie to PowerBI, so I'm having a hard time to get this working....

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

(different from dummy - only for context)

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.

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.

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)

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 made a sanitized dummy file with the expected outcome (in excel of course) but if you need any more clarification on this issue, please let us know!

Thanks in advance for your support!

[v-cgao-msft · Accepted solution]

Hi @talitanieps ,

My steps are as follows.
1. Create a dim table.

DimCourse = DISTINCT('Data_Base1'[course])

2. Relationships.

3. New measures.

2020 %pre = 
VAR _today =
    TODAY ()
VAR _day_last_year =
    EDATE ( TODAY (), -12 )
VAR _day =
    CALCULATE (
        MAX ( 'Data_Base1'[course_enrollment_day] ),
        'Data_Base1'[course_enrollment_day] <= _day_last_year
    )
VAR _result =
    CALCULATE (
        MAX ( 'Data_Base1'[course_percentage] ),
        'Data_Base1'[course_enrollment_day] = _day
    )
RETURN
_result

2021_% = 
MAXX ( ALL ( 'DimCourse' ), [2020 %pre] )

2022_% = 
CALCULATE (
    MAX ( 'Data_Base1'[course_percentage] ),
    'Data_Base1'[course_enrollment_day]
        = TODAY ()
)

4. New a table visual.

The PBIX file is attached for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[talitanieps · Accepted solution]

Hello Gao!

Thanks a lot for your prompt response - the solution is just about right!! 
We use the percentage attached to the date in order to return a sum/running total of the enrollments from the moment of the campaign started until point A in time, where point A is "today"; how could we show that running total in this sample you shared? 
I tried creating a running total measure, but I believe it shows all the time running total, not the amount related to the course percentage evolution......

Any ideas and/or suggestions on how we can make it happen?

[v-cgao-msft · Accepted solution]

Hi @talitanieps ,

My steps are as follows.
1. Create a dim table.

DimCourse = DISTINCT('Data_Base1'[course])

2. Relationships.

3. New measures.

2020 %pre = 
VAR _today =
    TODAY ()
VAR _day_last_year =
    EDATE ( TODAY (), -12 )
VAR _day =
    CALCULATE (
        MAX ( 'Data_Base1'[course_enrollment_day] ),
        'Data_Base1'[course_enrollment_day] <= _day_last_year
    )
VAR _result =
    CALCULATE (
        MAX ( 'Data_Base1'[course_percentage] ),
        'Data_Base1'[course_enrollment_day] = _day
    )
RETURN
_result

2021_% = 
MAXX ( ALL ( 'DimCourse' ), [2020 %pre] )

2022_% = 
CALCULATE (
    MAX ( 'Data_Base1'[course_percentage] ),
    'Data_Base1'[course_enrollment_day]
        = TODAY ()
)

4. New a table visual.

The PBIX file is attached for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[talitanieps · Accepted solution]

Hello Gao!

Thanks a lot for your prompt response - the solution is just about right!! 
We use the percentage attached to the date in order to return a sum/running total of the enrollments from the moment of the campaign started until point A in time, where point A is "today"; how could we show that running total in this sample you shared? 
I tried creating a running total measure, but I believe it shows all the time running total, not the amount related to the course percentage evolution......

Any ideas and/or suggestions on how we can make it happen?

[v-cgao-msft · Accepted solution]

Hi @talitanieps ,

The measure returns a scalar that returns different results in different filtering contexts, where do you place it?

Also this question is beyond the topic of the post, please consider tagging the answer and closing the thread. You may consider opening a new thread with the background of the calculation and the expected output. Thanks in advance!

Reference:

How to Get Your Question Answered Quickly

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data