Forum Discussion
Current month vs previous month comparison in table
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.
Hi manojk_pbi
Create measure using below dax.
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.
Hello mdaatifraza5556
I tried the sample provided by you but is not working in the main application. Then i realised there is a composite key "PRJ | MONTHYEAR" in the table. I am not getting the values now in the same row instead it is throwing in the another row.
This column is used to connect other tables to filter data based on project along with the rpeorting period.
Your advise is much appreciated.
| MONTHKEY | PRJ Name | Division | BU UNIT | ReportingDate | KPI |
| Jun-25|PRJ1 | PRJ1 | DIV1 | BU1 | 25-Jun | 60% |
| Jun-25|PRJ2 | PRJ2 | DIV1 | BU1 | 25-Jun | 85% |
| Jun-25|PRJ3 | PRJ3 | DIV1 | BU2 | 25-Jun | 79% |
| Jun-25|PRJ4 | PRJ4 | DIV1 | BU3 | 25-Jun | 93% |
| Jun-25|PRJ5 | PRJ5 | DIV1 | BU1 | 25-Jun | 85% |
| Jun-25|PRJ6 | PRJ6 | DIV2 | BU1 | 25-Jun | 85% |
| Jun-25|PRJ7 | PRJ7 | DIV2 | BU1 | 25-Jun | 60% |
| Jun-25|PRJ8 | PRJ8 | DIV2 | BU3 | 25-Jun | 60% |
| Jul-25|PRJ1 | PRJ1 | DIV1 | BU1 | 25-Jul | 85% |
| Jul-25|PRJ2 | PRJ2 | DIV1 | BU1 | 25-Jul | 60% |
| Jul-25|PRJ3 | PRJ3 | DIV1 | BU2 | 25-Jul | 60% |
| Jul-25|PRJ4 | PRJ4 | DIV1 | BU3 | 25-Jul | 60% |
| Jul-25|PRJ5 | PRJ5 | DIV1 | BU1 | 25-Jul | 60% |
| Jul-25|PRJ6 | PRJ6 | DIV2 | BU1 | 25-Jul | 60% |
| Jul-25|PRJ7 | PRJ7 | DIV2 | BU1 | 25-Jul | 85% |
| Jul-25|PRJ8 | PRJ8 | DIV2 | BU3 | 25-Jul | 43% |