Change in Data compared to previous month

[jsb81]

Hi,

I have the data in the table below which holds property rent charged data every month. I'm trying to compare the change in data between each month to find out:


1) No of new properties in the month

2) No of properties suspended in the month

3) No of properties rent increased

4) No of properties rent decreased

5) No of properties rent was same

Once the data is prepared I like to create a visual
 

Prop Code	Amount	Adjustments	Payment Due	Month	Year	Payment Date
1024	1408.33		1408.33	July	2016	31/07/2016
1025	1408.33		1408.33	July	2016	31/07/2016
1043	780		780	July	2016	31/07/2016
1044	866.67		866.67	July	2016	31/07/2016
1045	823.34		823.34	July	2016	31/07/2016
1047	1238.4		1238.4	July	2016	31/07/2016
1054	1430		1430	July	2016	31/07/2016
1090	1607.74		1607.74	July	2016	31/07/2016
1136	1820		1820	July	2016	31/07/2016
1153	1603.33		1603.33	July	2016	31/07/2016
1154	1911.9		1911.9	July	2016	31/07/2016
1833	1825	-1397	428	July	2016	31/07/2016
2058	2312	-57.67	2254.33	July	2016	31/07/2016
2415	2259.52		2259.52	July	2016	31/07/2016
3149	1781.55	-121	1660.55	July	2016	31/07/2016
3400	2389.88	-1320	1069.88	July	2016	31/07/2016
3474	217.26		217.26	July	2016	31/07/2016
3489	2253.33	35.36	2288.69	July	2016	31/07/2016
3701	2368.15		2368.15	July	2016	31/07/2016
3899	217.26		217.26	July	2016	31/07/2016
4072	3407.56	-1993.34	1414.22	July	2016	31/07/2016
4073	3186.23	-2340	846.23	July	2016	31/07/2016
4074	2404.63	-1646.67	757.96	July	2016	31/07/2016
1024	1408.33		1408.33	August	2016	31/08/2016
1025	1408.33		1408.33	August	2016	31/08/2016
1043	780		780	August	2016	31/08/2016
1044	866.67		866.67	August	2016	31/08/2016
1045	823.34		823.34	August	2016	31/08/2016
1047	1238.4		1238.4	August	2016	31/08/2016
1054	1430		1430	August	2016	31/08/2016
1090	1607.74		1607.74	August	2016	31/08/2016
1136	1820		1820	August	2016	31/08/2016
1153	1603.33		1603.33	August	2016	31/08/2016
1154	1911.9		1911.9	August	2016	31/08/2016
1458	1968.61	43.45	2012.06	August	2016	31/08/2016
1488	2123.33	86.66	2209.99	August	2016	31/08/2016
2093	2042.26	43.46	2085.72	August	2016	31/08/2016
2335	1300	86.67	1386.67	August	2016	31/08/2016
3964	2036.66	173.32	2209.98	August	2016	31/08/2016
3966	11916.75	-7150.05	4766.7	August	2016	31/08/2016
4058	4345.24		4345.24	August	2016	31/08/2016
4059	6500	-3250	3250	August	2016	31/08/2016
4060	6500	-3250	3250	August	2016	31/08/2016
4061	4506.68	-2253.34	2253.34	August	2016	31/08/2016
4075	2513.34		2513.34	August	2016	31/08/2016
4076	1473.34		1473.34	August	2016	31/08/2016
4077	2166.67		2166.67	August	2016	31/08/2016
4078	1516.67		1516.67	August	2016	31/08/2016
4079	417.18		417.18	August	2016	31/08/2016
1024	1408.33		1408.33	Sep	2016	30/09/2016
1025	1408.33		1408.33	Sep	2016	30/09/2016
1043	780		780	Sep	2016	30/09/2016
1044	866.67		866.67	Sep	2016	30/09/2016
1045	823.34		823.34	Sep	2016	30/09/2016
1047	1238.4		1238.4	Sep	2016	30/09/2016
1054	1430		1430	Sep	2016	30/09/2016
1090	1607.74		1607.74	Sep	2016	30/09/2016
1136	1820		1820	Sep	2016	30/09/2016
1153	1603.33		1603.33	Sep	2016	30/09/2016
1154	1911.9		1911.9	Sep	2016	30/09/2016
1921	2253.33	693.36	2946.69	Sep	2016	30/09/2016
2386	2094.3	237.58	2331.88	Sep	2016	30/09/2016
2987	1820	71.3	1891.3	Sep	2016	30/09/2016
3482	2231.66	260	2491.66	Sep	2016	30/09/2016
3609	2307.9	-1724.56	583.34	Sep	2016	30/09/2016
4018	2036.66	-2015.53	21.13	Sep	2016	30/09/2016
4026	1868.45	-1825	43.45	Sep	2016	30/09/2016
4096	2968.94		2968.94	Sep	2016	30/09/2016

Thanks
Jag

[v-sihou-msft]

@jsb81

I assume you analyze those data on month level. I suggest you add a Month Number column in your Table. Then you can create measures like below: 

1) No of new properties in the month

No of new properties in the month =
SUMX (
    VALUES ( Table[Prop Code] ),
    IF (
        CALCULATE (
            COUNTROWS ( Table ),
            FILTER ( ALL ( Table ), Table[PaymentDate] < MAX ( Table[PaymentDate] ) )
        )
            = 0
            && CALCULATE ( COUNTROWS ( Table ) ) = 1,
        1,
        0
    )
)

2) No of properties suspended in the month

No of properties suspended in the month =
SUMX (
    VALUES ( Table[Prop Code] ),
    IF (
        CALCULATE (
            COUNTROWS ( Table ),
            FILTER (
                ALL ( Table ),
                Table[Year] = MAX ( Table[Year] )
                    && Table[MonthNumber]
                        = MAX ( Table[MonthNumber] ) - 1
            )
        )
            > 0
            && CALCULATE ( COUNTROWS ( Table ) ) = 0,
        1,
        0
    )
)

3) No of properties rent increased

No of properties rent increased =
SUMX (
    VALUES ( Table[Prop Code] ),
    IF ( CALCULATE ( SUM ( Table[Adjustment] ) ) > 0, 1, 0 )
)

4) No of properties rent decreased

No of properties rent decreased =
SUMX (
    VALUES ( Table[Prop Code] ),
    IF ( CALCULATE ( SUM ( Table[Adjustment] ) ) < 0, 1, 0 )
)

5) No of properties rent was same

No of properties rent was same =
SUMX (
    VALUES ( Table[Prop Code] ),
    IF ( CALCULATE ( SUM ( Table[Adjustment] ) ) = BLANK (), 1, 0 )
)

Regards,

[jsb81]

Hi Shiou,

I'm pretty new to PowerBI and havent worked on complex measures. Could you please upload the powerbi file so i can get an idea how the measures have been applied.

Thanks

Jag