How to calculate running totals by using evolution in percentage? (PART II)

[talitanieps]

Hello again, folks!

In my previous topic, I didn't clarify why I needed the percentage comparison, and since only part of my issue is solved, I'm creating this new post to see if I can get some enlightment on the full thing ^^'

So, we need the enrollment running totals for each course, for each year, but the data we use to compare both is course_percentage; in the previous post I got a pretty good solution on how to get the percentages for each course, and after trying a lot on my own, I couldn't get it to work to show the running totals too.

In excel we did a VLOOKUP for the percetange (to retrieve the percentage related to today), and another VLOOKUP using that value to show the running total corresponding to that percentage.

VLOOKUP FOR THE PERCENTAGE:
=XLOOKUP("2021" &
[@Course] &
TODAY()-365;
Data_Base[[#All];[campaign]] &
Data_Base[[#All];[course]] &
Data_Base[[#All];[course_enrollment_day]];
Data_Base[[#All];[course_percentage]]
)

VLOOKUP FOR THE RUNNING TOTAL:
=XLOOKUP("2021" &

[@Course] &
[@[2021_%]];
Data_Base[[#All];[campaign]] &
Data_Base[[#All];[course]] &
Data_Base[[#All];[course_percentage]];
Data_Base[[#All];[course_rt]]
)

I modified the dummmy with some more explanation and a graph sample, as the end goal for this question. 🙂

Thanks so so much for your assistance in advance! 😄

Dummy updated
Original Post - How to compare running totals by using evolution in percentage?
Proposed Resolution - Download

[talitanieps]

Hello Jianbo! 😁

Once again, thanks a bunch for your response and assistance with this issue - it's been a big challenge for us on our own, and you're empowering us to solve it!! We are very very close to the results we need, but not just quite yet 😅

What is working perfectly so far:

2022_% measure ✔️

2021_% measure ✔️
2022_rt measure ✔️

For instance, for another course, this is what we get:

222% = 59,35 ✔️

221% = 59,56  ✔️

222_rt = 620 ✔️
221_rt = 626 ✖️

I looked it up in the db and found out that this 626 corresponds to:

Date 28/07/21

2021% = 38,25

course_rt = 626

So the 2021_rt measure is not getting the value based on the percentage, but rather on 2022_rt number, and bringing an approximate value.
( In numbers, 2021_rt should correspond to the running total from 0 up until 59,56)

This is what we are expecting:


Is there a way to, in this part of the code:

2021_rt = 
var _a = [2022_rt]
return CALCULATE(MIN('Data_Base1'[course_rt]),FILTER('Data_Base1',[campaign]=2021&&[course_rt]>=_a))

Instead we calculate the min value, we put a SUM or SUMX of enrollments_number? Or even another measure that calculates the running total for 2021, and then gets filtered by the conditions?

I'll keep trying to see if I get an 'eureka' moment for this measure, in this meantime - if you need any other info, please let me know! =]]]]

Thanks a bunch once again!

[v-jianboli-msft · Accepted solution]

Hi @talitanieps ,

I have modified the logic of the measures to calculate 2021 year's value:

Please try:

2022% = 
var _a = CALCULATE(MAX('Data_Base1'[course_enrollment_day]),FILTER('Data_Base1',[course_enrollment_day]<=TODAY()&&[campaign]=2022))
return CALCULATE(MAX('Data_Base1'[course_percentage]),FILTER('Data_Base1',[course_enrollment_day]=_a))

2021% = 
var _a =[2022%]
return CALCULATE(MIN('Data_Base1'[course_percentage]),FILTER('Data_Base1',[campaign]=2021&&[course_percentage]>=_a))

2022_rt = 
var _a = CALCULATE(MAX('Data_Base1'[course_enrollment_day]),FILTER('Data_Base1',[course_enrollment_day]<=TODAY()&&[campaign]=2022))
return CALCULATE(MAX('Data_Base1'[course_rt]),FILTER('Data_Base1',[course_enrollment_day]=_a))

2021_rt = 
var _a = [2022_rt]
return CALCULATE(MIN('Data_Base1'[course_rt]),FILTER('Data_Base1',[campaign]=2021&&[course_rt]>=_a))

Output:

Best Regards,

Jianbo Li

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

[talitanieps]

Hello Jianbo!

A colleague of mine was helping me with it and, after tweaking the 2021_rt measure, we got the result I was expecting!!

212_rt =
 var _a = [212%]
 return CALCULATE(MIN('Data_Base1'[course_rt]),FILTER('Data_Base1',[campaign]=2021&&[course_percentage]=_a))

So, instead of using the 222_r measure, we used the percentage measure, and instead of course_rt at the end of the return, we put course_percentage, as the item to be filtered!

It worked like a charm, so, there we are.
Thanks a lot once again for your help on this, we couldn't have done it without you! 😍😆🤓

[talitanieps]

Hello Jianbo!!

Thanks thanks thanks so so so  much! 👏
I modified the measures as per your comment and here's what I got :

However, I noticed something: we are using the date today one year ago to get the percentage number, but in fact we use if for reference only; in excel, it works as a reference column, in order to bring the expected result (that is, the closest percentage to today's percentage). What the measre is doing is returning the exact percentage that the course had, one year ago..... and that's not quite there yet (I know, frustrating, right?).

Let's take one course as an example..... This is what the measure is showning - the exact percentage in 08/26/21:

And according to the dataset, the percentage that is closest to today's (56,13) is 56,28 (related to 08/22/21); 
 

Today we have that percentage, which is incorrectly shown by the measure (but only because it's considering the same date last year to show exactly that result, not an approximated one):

While, in excel, this is the percentage shown by using the vlookup formula in the description of the problem:

        2022                      2021

So, summing up, we use the date as a reference to get the percentage number, but we compare it to today's percentage and return the greater or equal value.

I tried changing some fields on the dax measure, in order to see if I can get it show me what I need, but I still have nothing... I believe some deeper knowledge on how PBI works is lacking me. I'll keep trying, though, who knows ehehehehe
Thanks once again for your assistance Jianbo, you've been very kind in assisting me with this.... I really appreciate it!

[v-jianboli-msft]

Hi @talitanieps ,

I forgot to add something to the 2021's calculation before, I have changed it now.

Please modify 2021's calculation:

2021_rt = 
var _a = CALCULATE(MAX('Data_Base1'[course_enrollment_day]),FILTER('Data_Base1',[course_enrollment_day]<=EDATE(TODAY(),-12)&&[campaign]=2021))
return CALCULATE(MAX('Data_Base1'[course_rt]),FILTER('Data_Base1',[course_enrollment_day]=_a))

2021% = 
var _a = CALCULATE(MAX('Data_Base1'[course_enrollment_day]),FILTER('Data_Base1',[course_enrollment_day]<=EDATE(TODAY(),-12)&&[campaign]=2021))
return CALCULATE(MAX('Data_Base1'[course_percentage]),FILTER('Data_Base1',[course_enrollment_day]=_a))

Final output:

Best Regards,

Jianbo Li

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

[v-jianboli-msft]

Hi @talitanieps ,

Please try:

2021_rt = 
var _a = CALCULATE(MAX('Data_Base1'[course_enrollment_day]),FILTER('Data_Base1',[course_enrollment_day]<=EDATE(TODAY(),-12)&&[campaign]=2021))
return CALCULATE(MAX('Data_Base1'[course_rt]),FILTER('Data_Base1',[course_enrollment_day]=_a)

2022_rt = 
var _a = CALCULATE(MAX('Data_Base1'[course_enrollment_day]),FILTER('Data_Base1',[course_enrollment_day]<=TODAY()&&[campaign]=2022))
return CALCULATE(MAX('Data_Base1'[course_rt]),FILTER('Data_Base1',[course_enrollment_day]=_a))

2021% = 
var _a = CALCULATE(MAX('Data_Base1'[course_enrollment_day]),FILTER('Data_Base1',[course_enrollment_day]<=EDATE(TODAY(),-12)&&[campaign]=2021))
return CALCULATE(MAX('Data_Base1'[course_percentage]),FILTER('Data_Base1',[course_enrollment_day]=_a))

2022% = 
var _a = CALCULATE(MAX('Data_Base1'[course_enrollment_day]),FILTER('Data_Base1',[course_enrollment_day]<=TODAY()&&[campaign]=2022))
return CALCULATE(MAX('Data_Base1'[course_percentage]),FILTER('Data_Base1',[course_enrollment_day]=_a))

Final output:

Best Regards,

Jianbo Li

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

[talitanieps]

Hello Jianbo,

Thanks so much for your quick reply! It's so close to what we need that I'm super excited to see the results!
But....
When I tried to replicate that in our dashboard, I got:

For 2022%, it was able to get the percentage correctly, however for 2021% it's showing 100% for all the courses....I tried replicating that to another dashboard that needs the same calculation, but I got the same result: showing the percentage as 100% .
Do you have any idea or suggestion on how to fix it?