Forum Discussion

s2anya's avatar
s2anya
Frequent Visitor
8 years ago

Adding a previous and current 12 month column

Following is the data sample we are dealing with , which is having 27 months data. From which we need to extract 12 months data and show it as defined in the output dataset.
We are not able to add the previous and current column as per our requirement.

 

Data Sample-

DateKPIState
10/1/16 12:00 AM20AK
11/1/16 12:00 AM13AL
12/1/16 12:00 AM13AR
1/1/17 12:00 AM20AZ
2/1/17 12:00 AM14CA
3/1/17 12:00 AM10AK
4/1/17 12:00 AM13AL
5/1/17 12:00 AM12AR
6/1/17 12:00 AM10AZ
7/1/17 12:00 AM20CA
8/1/17 12:00 AM11AK
9/1/17 12:00 AM10AL
10/1/15 12:00 AM13AR
11/1/16 12:00 AM15AK
12/1/14 12:00 AM20AL
2/1/14 12:00 AM15AR
3/1/16 12:00 AM11AZ
5/1/15 12:00 AM15CA


Output-

StateOct-16Nov-16Dec-16Jan-17Feb-17Mar-17Apr-17May-17Jun-17Jul-17Aug-17Sep-17Previous 12 months
(10/2015-9/2016)		Current 12 months
(10/2016-9/2017)		% Change
AK2837274540413542714934552945040.714285714
AL242200322242230409205260343235319481194234880.796086509
AR618381927011877871121039213652011121.138461538
AZ212228282223227312269262313282293448186533510.796782842
CA12011209177112861345175913591528190815081585220212851186610.452104895
Need Help

3 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi s2anya,





    If I understand you correctly, I don't think there is an easy way to do it in Power BI currently. You may need to firstly create 12 measures to extract the 12 months' data separately.

     

     

    Month 1 =
VAR maxYM =
    CALCULATE ( MAX ( Table1[YearMonth] ), ALL ( Table1 ) )
RETURN
    CALCULATE (
        SUM ( Table1[KPI] ),
        FILTER ( ALLEXCEPT ( Table1, Table1[State] ), Table1[YearMonth] = maxYM )
    )

    Month 2 =
VAR maxYM =
    CALCULATE ( MAX ( Table1[YearMonth] ), ALL ( Table1 ) )
RETURN
    CALCULATE (
        SUM ( Table1[KPI] ),
        FILTER ( ALLEXCEPT ( Table1, Table1[State] ), Table1[YearMonth] = maxYM - 1 )
    )

                           .

     

                                  .

                                  .

     

    Month 12 =
VAR maxYM =
    CALCULATE ( MAX ( Table1[YearMonth] ), ALL ( Table1 ) )
RETURN
    CALCULATE (
        SUM ( Table1[KPI] ),
        FILTER ( ALLEXCEPT ( Table1, Table1[State] ), Table1[YearMonth] = maxYM - 11 )
    )

     

    And create another 3 measures to calculate previous and current 12 month's KPI and their changes.

     

    Current 12 months =
VAR maxYM =
    CALCULATE ( MAX ( Table1[YearMonth] ), ALL ( Table1 ) )
RETURN
    CALCULATE (
        SUM ( Table1[KPI] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[State] ),
            Table1[YearMonth]
                > maxYM - 12
                && Table1[YearMonth] <= maxYM
        )
    )

    Previous 12 months =
VAR maxYM =
    CALCULATE ( MAX ( Table1[YearMonth] ), ALL ( Table1 ) )
RETURN
    CALCULATE (
        SUM ( Table1[KPI] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[State] ),
            Table1[YearMonth]
                > maxYM - 24
                && Table1[YearMonth]
                <= maxYM - 12
        )
    )

    Change % = DIVIDE([Current 12 months]-[Previous 12 months],[Previous 12 months])

     

    Then you should be able to show State column as Rows, and all the 15 measures as Values on the Matrix visual to get the expected result in your scenario. :smileyhappy:

     

     

    Regards

    • s2anya's avatar
      s2anya
      Frequent Visitor
      8 years ago

      Hi v-ljerr-msft,

       

      Thanks for a quick response.

      One question here, What about the column names? Cannot leave it as month1, month2 etc. It should be a date.

       

      Regards

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        8 years ago

        Hi s2anya,

        One question here, What about the column names? Cannot leave it as month1, month2 etc. It should be a date.

        I understand that. But I don't think there is a way show the date as name for these measures directly. You may need to change the measure name manually. :smileyhappy:

         

        Regards