Forum Discussion

manojk_pbi's avatar
manojk_pbi
Icon for Helper V rankHelper V
1 year ago
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...
Need Help
Show and Tell
Tips and Tricks
  • mdaatifraza5556's avatar
    mdaatifraza5556
    1 year ago

    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.

  • mdaatifraza5556's avatar
    mdaatifraza5556
    1 year ago

    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's avatar
manojk_pbi
Icon for Helper V rankHelper V
1 year ago

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's avatar
mdaatifraza5556
Icon for Super User rankSuper User
1 year ago

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.