Forum Discussion
Helper VCurrent 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.
Super UserHi 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.
2. Create three measure using below dax.
------------------------------------------------------------------------------------
Result
If this answers your questions, kindly accept it as a solution and give kudos.
Helper VHow can we modify the KPI Comparison, not to show anything in comparison and no arrows when prev is not present.
- mdaatifraza5556
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.
- manojk_pbi
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%