Current month vs previous month comparison in table

[manojk_pbi]

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

[mdaatifraza5556 · Accepted solution]

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] )

RETURN

CALCULATE (

    AVERAGE ( 'Table'[KPI] ),

    FILTER (

        ALL ( 'Dim_Date' ),

        'Dim_Date'[YearMonth] = FORMAT ( SelectedMonth, "YYYYMM" )

    )

)

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

KPI_PrevMonth =

VAR SelectedMonth = MAX ( 'Dim_Date'[Date] )

RETURN

CALCULATE (

    AVERAGE ( 'Table'[KPI] ),

    FILTER (

        ALL ( 'Dim_Date' ),

        'Dim_Date'[YearMonth] =

            FORMAT ( EOMONTH ( SelectedMonth, -1 ), "YYYYMM" )

    )

)

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

KPI_Comparison =

[KPI_CurrMonth] - [KPI_PrevMonth]

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

For the icon 

Click on chart then go for the conditinal formatting for KPI_Comparison

Result

If this answers your questions, kindly accept it as a solution and give kudos.

[manojk_pbi]

@mdaatifraza5556 , thanks for quick response and clarification on my doubts. The solution helped lot

[mdaatifraza5556 · Accepted solution]

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] )

RETURN

CALCULATE (

    AVERAGE ( 'Table'[KPI] ),

    FILTER (

        ALL ( 'Dim_Date' ),

        'Dim_Date'[YearMonth] = FORMAT ( SelectedMonth, "YYYYMM" )

    )

)

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

KPI_PrevMonth =

VAR SelectedMonth = MAX ( 'Dim_Date'[Date] )

RETURN

CALCULATE (

    AVERAGE ( 'Table'[KPI] ),

    FILTER (

        ALL ( 'Dim_Date' ),

        'Dim_Date'[YearMonth] =

            FORMAT ( EOMONTH ( SelectedMonth, -1 ), "YYYYMM" )

    )

)

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

KPI_Comparison =

[KPI_CurrMonth] - [KPI_PrevMonth]

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

For the icon 

Click on chart then go for the conditinal formatting for KPI_Comparison

Result

If this answers your questions, kindly accept it as a solution and give kudos.

[manojk_pbi]

How can we modify the KPI Comparison,  not to show anything in comparison and no arrows when prev is not present.

[mdaatifraza5556 · Accepted solution]

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%

[manojk_pbi]

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 ? 

[mdaatifraza5556]

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.