value of same semester-to-date last year

[jsbourni]

Hi,

I'm using educational data. Did not quite found a similar example, so here's what I'm struggling to achieve. First, some context. I have 3 tables: calendar, semesters and students. Semesters cannot be included in dates because it generates conflicts. Students complete an admission form prior to the semester, so semesters encompasses 2 date ranges: admission and registration.

The calendar is linked to students' dates, semesters are linked to students' semesters.

My goal is to calculate a semester-to-date and compare it to the same previous semester (automn 2023 to autumn 2022, noted 20233 and 20223 respectively.

I use this to calculate the current or selected semester:

CALCULATE (

    COUNT( 'SSRS - Suivi des décisions'[cdperm] ),

    USERELATIONSHIP(Dates[Date], 'SSRS - Suivi des décisions'[date_dem_adm]),

    FILTER ( ALLSELECTED('SSRS - Suivi des décisions'), 'SSRS - Suivi des décisions'[date_dem_adm] <= MAX ( 'SSRS - Suivi des décisions'[date_dem_adm]) 

I tried fitting something like "selectedvalue(semester[semester]) -10" in a new measure to force the slicer selection to substract 10 (20233 - 10 = 20223). Not working. The second measure should also be a cumulative count.

Any advice?

)

[amitchandak · Accepted solution]

@jsbourni , If can create year and semester no in your date table and table

Like Summer =1, automn  2

The create a key = [Year] &"-"& [semester no]

Create a new table Period(if you do not have date table) with distinct [Year],[semester no], [Key]

Join both tables on key /date

and new column Date or Period table

Period Rank = RANKX((Period),Period[Key],,ASC,Dense)

This Period = CALCULATE(countrows(Table) , FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(countrows(Table) , FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))

Last Period same period= CALCULATE(countrows(Table) , FILTER(ALL(Period),Period[Year]=max(Period[Year])-1 && Period[Period]=max(Period[Period])))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[jsbourni]

hi,

Thank you for your answer. I have some tweaking to do, but I think it's a good solution.

best,