Forum Discussion
Solved
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 get...
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.
mdaatifraza5556 , thanks for quick response and clarification on my doubts. The solution helped lot