Value of Measure against previous period

[nkurian]

Hi,

*I am a newbie, so it is very much possible that this is a silly/easy question*

I have a table that shows gives me students enrolments into courses . There is a enrolment date as well.

I have added a measure Number of Enrolments = countA('Student Enrolments'[UserName]).

If I add a slicer with Enrolment Date, I can pick the year for which I need the enrolment count. I am stuck on the following requirement.

If I pick a year to get the count of enrolments for the year I need to get the growth % with respect to last year. 

Example, if I pick year 2016 in the time slicer, I should get the following.

Year-Selected Previous-Yr Growth %

120              100                   20

Thanks,

Ninu

[v-qiuyu-msft · Accepted solution]

Hi @nkurian,

In your scenario, you can create a new table group on [Enrolment Date] column and return the number of the [UserName] for each year. Then you can add calculated columns to return growth value. See:

Table = SUMMARIZE(Table1,Table1[Enrolment Date],"count",COUNTA(Table1[UserName]))

CountPrevious = calculate(SUM('Table'[Count]),filter('Table','Table'[Enrolment Date]=EARLIER('Table'[Enrolment Date])-1))

Growth = if('Table'[CountPrevious]=0, BLANK(), DIVIDE('Table'[Count]-'Table'[CountPrevious],'Table'[CountPrevious]))

If I misunderstand your requirement, please share some sample data and expected results for our analysis.

Best Regards,
Qiuyun Yu

[nkurian]

Hi @v-qiuyu-msft

Thanks for the suggestion. The enrolment Date is a date. So, I extracted the Enrolment Year into a custom column in the base table and your suggestion worked beautifully! Thanks much!

-Ninu

[ankitpatira]

@nkurian Assuming you have data in format of,

Student,EnrolDate

1,01/01/2015

2,01/01/2016

you can first add Index column in power bi desktop query editor by going to edit queries and under Add Column tab click Add Index Column.

Then created calculated column PreviousYearEnrol as 

PreviousYearEnrol =
IF(
'YOURTABLE'[IndexColumn] = 0,
'YOURTABLE'[student],
LOOKUPVALUE(
'YOURTABLE'[student],
'YOURTABLE'[IndexColumn],
'YOURTABLE'[IndexColumn]-1)
)

Then another calculated column PercentageDifference as

PercentageDiffernece = YOURTABLE[student] / sam[PreviousYearEnrol].

[nkurian]

@ankitpatira,

Unfortunately, the suggestion provided does not meet the requirement. The requirement is  to get count the number of records( enrolments in this case) for the selected year and also provide the count of records on the previous year.

I have a measure that counts the records and if I put that measure in the table visual with year field, it gives me the count grouped by year. But my requirement is that when I select a particular year, say via a slicer visual, it should show me the count of enrolments from that year AND from previous year and the growth percentage.

Thanks,

Ninu