Cumulative total on previous custom period

[jsbourni]

Hi,

I have 3 tables from which I want to compare the number of registered students for similar semesters, i.e. the equivalent semester the year before. For example, on this day I want to calculate the number of registered students for the summer semester (20242) and I want to retrieve the cumulative count for the same day last year for the students registered to (20232). The tables are semesters, Registration and Dates (not shown).

Semesters
semester
20231
20232
20233
20241
20242
20243

Registration
ID	semester	registration date
1	20232	03/16/2023
2	20232	03/15/2023
3	20242	03/01/2024
4	20242	03/03/2024
5	20242	03/04/2024

I think my semantic model is not quite standard as my semesters are out of the dates table. The reason is that students register prior to the semester (see registration date). Otherwise, the cumulative count would have to fall on the interval fixed in the dates table. I'm not totally convinced myself, you may argue on that. Semesters is related to Registration, and Registration is related to Dates. Registration periods vary over time for many reasons and I have to retrieve the MIN and MAX dates for each semester in order to get the cumulative count.

My aim is to select the current semester (slicer) and get both cumulative counts in cards (or in a line chart. The problem is the X axis). Here's one of my numerous attempts :

VAR currentSem = SELECTEDVALUE(Semesters[semester])
VAR prevSem = currentSem - 10

RETURN
CALCULATE (
    DISTINCTCOUNT( Registration[ID] ),
    FILTER(Registration, Registration[semester] = prevSem )
)

As you see, I'm not using the dates table here as the registration date is already in the Registration table. Can't figure how to force the cumulative count to use the TODAY() function.

The desired output would be :

• Cumulative count as of today for 20242 = 3
• Cumulative count at the same date last year for the 20232 semester = 2

Thanks for your help. Don't hesitate to comment on the model, i.e. having the semesters outside the dates table.

[v-yangliu-msft]

Hi  @jsbourni ,

Thanks for the reply from @amitchandak , please allow me to provide another insight:

If you want to calculate based on the TODAY() function, you can use the following dax.

All =
var _today=TODAY()
var _todaysemester=
MAXX(
    FILTER(ALL('Registration'),
    YEAR('Registration'[registration date])=YEAR(_today)&&MONTH('Registration'[registration date])=MONTH(_today)),[semester])
var _Thissemester=
CALCULATE(DISTINCTCOUNT('Registration'[ID]),
FILTER(ALL('Registration'),
'Registration'[semester]=_todaysemester))
var _last=
MAXX(
    FILTER(ALL('Registration'),
    YEAR('Registration'[registration date])=YEAR(_today)-1&&MONTH('Registration'[registration date])=MONTH(_today)),[semester])
var _Lastsemester=
CALCULATE(DISTINCTCOUNT('Registration'[ID]),
FILTER(ALL('Registration'),
'Registration'[semester]=_last))
return
_Thissemester-_Lastsemester

For cumulative, you can use the following functions:

cumulative =
SUMX(
    FILTER(ALL('Registration'),
    'Registration'[registration date]<=MAX('Registration'[registration date])),[All])

Best Regards,

Liu Yang

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

[jsbourni]

Hi @v-yangliu-msft,

Thank you for your suggestion. However, it does not seem to perform what I'm looking for. I get a negative number out of the substraction and the cumulative count (SUMX in your code) gathers all data.

I will try to narrow the scope a bit. In fact, I have a slicer in the page which allows the selection of semesters. The user can follow registration in the current and the next semesters as well as look back to see the final count of previous semesters.

I'd like a cumulative count that refers to the selected value of the slicer. Let's say registration for Autumn 2024 are on. I would select the slicer valuer 20243 and a card would display the cumlative count as of today. The card next to it would show the cumulative count up to 03/19/2023 for the previous equivalent (autumn) semester, 20233. Basically, it's one year before or sameperiodlast year, but the cumulative count is conditioned by the variable MIN and MAX dates of each semester. I can get the cumulative count:

Totalytd Registrations= 
VAR CumulCount =
CALCULATE (
    DISTINCTCOUNT('Registration[ID] ),
    FILTER ( ALLSELECTED('Registration'), 'Registration'[date] <= MAX ( 'Registration'[date] )
) )

RETURN
IF( MAX ('Registration'[date] ) <= TODAY(), CumulCount, BLANK() )

For the previous semester I tried the following bit, but I could not modify the context enough to get what I want and the calculation always show the same results as if the formula does not retrieve the right date interval for the previous semester.

VAR currentSemester = SELECTEDVALUE(Semesters[semester])
VAR previousSemester = currentSemester - 10

I hope that I helped clarify tinghs a bit. Thanks.

[amitchandak]

@jsbourni , Assuming Semesters table is joined with Registration table

Create a new column in semester Table

Semesters Rank = RANKX(all(Semesters),Semesters[Semesters],,ASC,Dense)

Now create measures
This Semesters= CALCULATE(DISTINCTCOUNT( Registration[ID] ), FILTER(ALL(Semesters),Semesters[Semesters]=max(Semesters[Semesters]) ))

Last Semesters= CALCULATE(DISTINCTCOUNT( Registration[ID] ), FILTER(ALL(Semesters),Semesters[Semesters]=max(Semesters[Semesters])-1 ))

Power BI Custom Period Till Date (PTD)- https://youtu.be/rQ3Z_LtxwQM

[jsbourni]

Hello @amitchandak ,

Thank you for your quick answer. Unfortunately, it does not cumulate the registrations. Here's what I see (for some reason dates are not showing properly, but they are ascending):

Date	This semester	Last semester	Total registration
###########	1		1
###########	597	204	597
###########	224	188	224
###########	187	190	187
###########	234	127	234

The cumulative column shows the same count as the total registrations. Thanks.