Forum Discussion
Current month vs previous month comparison in table
Hello,
I have single table with facts and dimensions data. I need to have table to show the kpis of projects for the current month and also the previous month along with indicators.
I am not getting how we can get the previous months kpi data along with current month when reporting month selected for current by user.
Attaching the sample data for referecne
| PRJ Name | Division | BU UNIT | ReportingDate | KPI |
| PRJ1 | DIV1 | BU1 | 25-Jun | 60% |
| PRJ2 | DIV1 | BU1 | 25-Jun | 85% |
| PRJ3 | DIV1 | BU2 | 25-Jun | 79% |
| PRJ4 | DIV1 | BU3 | 25-Jun | 93% |
| PRJ5 | DIV1 | BU1 | 25-Jun | 85% |
| PRJ6 | DIV2 | BU1 | 25-Jun | 85% |
| PRJ7 | DIV2 | BU1 | 25-Jun | 60% |
| PRJ8 | DIV2 | BU3 | 25-Jun | 60% |
| PRJ1 | DIV1 | BU1 | 25-Jul | 85% |
| PRJ2 | DIV1 | BU1 | 25-Jul | 60% |
| PRJ3 | DIV1 | BU2 | 25-Jul | 60% |
| PRJ4 | DIV1 | BU3 | 25-Jul | 60% |
| PRJ5 | DIV1 | BU1 | 25-Jul | 60% |
| PRJ6 | DIV2 | BU1 | 25-Jul | 60% |
| PRJ7 | DIV2 | BU1 | 25-Jul | 85% |
| PRJ8 | DIV2 | BU3 | 25-Jul | 43% |
Expected output: - Comparison is diff of Curr vs Prev
Hi manojk_pbi
Can you please try the below steps to get your result ?
1. Create dim date table table and in that create a calculated column.YearMonth = FORMAT(Dim_Date[Date], "YYYYMM")
2. Create three measure using below dax.KPI_CurrMonth =VAR SelectedMonth = MAX ( 'Dim_Date'[Date] )RETURNCALCULATE (AVERAGE ( 'Table'[KPI] ),FILTER (ALL ( 'Dim_Date' ),'Dim_Date'[YearMonth] = FORMAT ( SelectedMonth, "YYYYMM" )))----------------------------------------------------------------------------------------------------KPI_PrevMonth =VAR SelectedMonth = MAX ( 'Dim_Date'[Date] )RETURNCALCULATE (AVERAGE ( 'Table'[KPI] ),FILTER (ALL ( 'Dim_Date' ),'Dim_Date'[YearMonth] =FORMAT ( EOMONTH ( SelectedMonth, -1 ), "YYYYMM" )))------------------------------------------------------------------------------------
KPI_Comparison =[KPI_CurrMonth] - [KPI_PrevMonth]--------------------------------------------------------------------------------------For the iconClick on chart then go for the conditinal formatting for KPI_ComparisonResult
If this answers your questions, kindly accept it as a solution and give kudos.
Hi manojk_pbi
Create measure using below dax.KPI_Comparison_1 =IF (NOT ISBLANK ( [KPI_PrevMonth] ),[KPI_CurrMonth] - [KPI_PrevMonth])Case1. when PreMth value is there
Case 2. If PrevMth values is not there
If this answers your questions, kindly accept it as a solution and give kudos.
7 Replies
Hi manojk_pbi
Can you please try the below steps to get your result ?
1. Create dim date table table and in that create a calculated column.YearMonth = FORMAT(Dim_Date[Date], "YYYYMM")
2. Create three measure using below dax.KPI_CurrMonth =VAR SelectedMonth = MAX ( 'Dim_Date'[Date] )RETURNCALCULATE (AVERAGE ( 'Table'[KPI] ),FILTER (ALL ( 'Dim_Date' ),'Dim_Date'[YearMonth] = FORMAT ( SelectedMonth, "YYYYMM" )))----------------------------------------------------------------------------------------------------KPI_PrevMonth =VAR SelectedMonth = MAX ( 'Dim_Date'[Date] )RETURNCALCULATE (AVERAGE ( 'Table'[KPI] ),FILTER (ALL ( 'Dim_Date' ),'Dim_Date'[YearMonth] =FORMAT ( EOMONTH ( SelectedMonth, -1 ), "YYYYMM" )))------------------------------------------------------------------------------------
KPI_Comparison =[KPI_CurrMonth] - [KPI_PrevMonth]--------------------------------------------------------------------------------------For the iconClick on chart then go for the conditinal formatting for KPI_ComparisonResult
If this answers your questions, kindly accept it as a solution and give kudos.
Hello mdaatifraza5556 , thanks lot for your quick reply and the solution. Great !!.
I have one question, do we need to have separate Date table why cann't we use the date from the same table ?
Hi manojk_pbi
It will also work, but it is a best practice to create a separate Date table.
In some cases, especially when using time intelligence functions it works more reliably and ensures proper results.If this answers your questions, kindly accept it as a solution and give kudos.
How can we modify the KPI Comparison, not to show anything in comparison and no arrows when prev is not present.
Hi manojk_pbi
Create measure using below dax.KPI_Comparison_1 =IF (NOT ISBLANK ( [KPI_PrevMonth] ),[KPI_CurrMonth] - [KPI_PrevMonth])Case1. when PreMth value is there
Case 2. If PrevMth values is not there
If this answers your questions, kindly accept it as a solution and give kudos.
mdaatifraza5556 , thanks for quick response and clarification on my doubts. The solution helped lot
