KPI Measure - Monthly Comparison

[sirramzy]

Hi All,

Beginner to Power Bi, been using it for a few weeks now. Got a prett basic query.

I'll call my fact table as 'Customer Registration Table' which has the following fields

1. Registration Date
2. Membership Id
3. Customer Profile Details.....

I created a Calendar table with continuous dates with the following fields:

1. Date - (Calendar = CALENDAR(MIN(Enrollment,Enrollment[Enrollment Date]),MAX(Enrollment,Enrollment[Enrollment Date]))
2. Month No.-   FORMAT('Calendar'[Date],"mm")
3. Month Name - 'Calendar'[Date].[Month]
4. Year - FORMAT('Calendar'[Date],"yyyy")

After linking the Calendar table with the fact table (Customer Registration Table) i have been able to achieve some basic dashboard objectives.

I have also created the following Measures in the Customer Registration Table for comparitive analysis, using the reference materials available in this forum

1. MTD Registration Count = CALCULATE([TotalEnrolments],DATESMTD('Calendar'[Date]))
2. YTD Registration Count = CALCULATE([TotalEnrolments],DATESYTD('Calendar'[Date]))
3. Total Registration Count = DISTINCTCOUNT(Enrollment[Membership ID])
4. Previous Month Count = CALCULATE([TotalEnrolments],DATEADD('Calendar'[Date],-1,MONTH))

I am however unable to do the following:

I want to create a KPI measure of Enrolment Count basis every time i open the report, on an MTD basis - comparing it to the      Previous Month for the same period.

I have put my MTD Registration Count as indicator, Previous Month Count as Target Goals and Year in the trend Axis. While i am gettting the current value correctly, it is showing the goal seek value for the entire 2017 period

I.e. the KPI tile shows current value as 29 and goal as 371

Reference Table:

                Current Month     Previous Month

June          29                           51

May          51                           18

April         18                           169

March      169                         71

February   71                           22  

January     22                           40

----------------------------------------

SubTotal  360                        371       

Request guidance.

[v-huizhn-msft]

Hi @sirramzy,

Please create a year-month columnm, then add the year-month column as Trend axis. Please see my following screenshot, it shows the expected result.



Best Regards,
Angelia

[sirramzy]

@v-huizhn-msft and @cs_skit

I tried using your solutions, and there is an improvement! Only it is showing the MTD and Previous Month count of 2016, instead of 2017

I must be doing an elementary mistake somewhere, that i am unable to see. I am attaching the sample for reference. Can you please guide the right way of doing it ?

URL : Sample File

Regards,

SR

[sirramzy]

any suggestions please?

[cs_skit]

You could do calculated fields in your Calendar table like "Year Month Int" with values like 201706

And a measures in Calendar table ThisMonth and LastMonth

having that makes it easier then you can do

TestMeasure = COUNTROWS(FILTER(ALL('Table');RELATED(Calendar[YearMonthInt])=[ThisMonth]))

Having some measures and calc fields for custom time intelligence makes other DAX easier and you can even easily reuse it between projets. Especially important when you have finance data with Fiscal Years and all that but even helpful with normal time specific data.

there is a nice function here http://databear.com/2016/11/08/power-bi-tip-dynamic-calendar-table/ that I use as basis for my calendar table it already comes with some good stuff