Adding a previous and current 12 month column

[s2anya]

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-

Date	KPI	State
10/1/16 12:00 AM	20	AK
11/1/16 12:00 AM	13	AL
12/1/16 12:00 AM	13	AR
1/1/17 12:00 AM	20	AZ
2/1/17 12:00 AM	14	CA
3/1/17 12:00 AM	10	AK
4/1/17 12:00 AM	13	AL
5/1/17 12:00 AM	12	AR
6/1/17 12:00 AM	10	AZ
7/1/17 12:00 AM	20	CA
8/1/17 12:00 AM	11	AK
9/1/17 12:00 AM	10	AL
10/1/15 12:00 AM	13	AR
11/1/16 12:00 AM	15	AK
12/1/14 12:00 AM	20	AL
2/1/14 12:00 AM	15	AR
3/1/16 12:00 AM	11	AZ
5/1/15 12:00 AM	15	CA

Output-

State	Oct-16	Nov-16	Dec-16	Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Previous 12 months (10/2015-9/2016)	Current 12 months (10/2016-9/2017)	% Change
AK	28	37	27	45	40	41	35	42	71	49	34	55	294	504	0.714285714
AL	242	200	322	242	230	409	205	260	343	235	319	481	1942	3488	0.796086509
AR	61	83	81	92	70	118	77	87	112	103	92	136	520	1112	1.138461538
AZ	212	228	282	223	227	312	269	262	313	282	293	448	1865	3351	0.796782842
CA	1201	1209	1771	1286	1345	1759	1359	1528	1908	1508	1585	2202	12851	18661	0.452104895

[v-ljerr-msft]

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. 

Regards

[s2anya]

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]

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.

Regards