Change in Data compared to previous month

Question

Hi,&nbsp;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 month2) No of properties suspended in the month3) No of properties rent increased4) No of properties rent decreased5) No of properties rent was same&nbsp;Once the data is prepared I like to create a visual&nbsp;Prop CodeAmountAdjustmentsPayment DueMonthYearPayment Date10241408.33&nbsp;1408.33July201631/07/201610251408.33&nbsp;1408.33July201631/07/20161043780&nbsp;780July201631/07/20161044866.67&nbsp;866.67July201631/07/20161045823.34&nbsp;823.34July201631/07/201610471238.4&nbsp;1238.4July201631/07/201610541430&nbsp;1430July201631/07/201610901607.74&nbsp;1607.74July201631/07/201611361820&nbsp;1820July201631/07/201611531603.33&nbsp;1603.33July201631/07/201611541911.9&nbsp;1911.9July201631/07/201618331825-1397428July201631/07/201620582312-57.672254.33July201631/07/201624152259.52&nbsp;2259.52July201631/07/201631491781.55-1211660.55July201631/07/201634002389.88-13201069.88July201631/07/20163474217.26&nbsp;217.26July201631/07/201634892253.3335.362288.69July201631/07/201637012368.15&nbsp;2368.15July201631/07/20163899217.26&nbsp;217.26July201631/07/201640723407.56-1993.341414.22July201631/07/201640733186.23-2340846.23July201631/07/201640742404.63-1646.67757.96July201631/07/201610241408.33&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1408.33August201631/08/201610251408.33&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1408.33August201631/08/20161043780&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;780August201631/08/20161044866.67&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;866.67August201631/08/20161045823.34&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;823.34August201631/08/201610471238.4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1238.4August201631/08/201610541430&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1430August201631/08/201610901607.74&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1607.74August201631/08/201611361820&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1820August201631/08/201611531603.33&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1603.33August201631/08/201611541911.9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1911.9August201631/08/201614581968.6143.452012.06August201631/08/201614882123.3386.662209.99August201631/08/201620932042.2643.462085.72August201631/08/20162335130086.671386.67August201631/08/201639642036.66173.322209.98August201631/08/2016396611916.75-7150.054766.7August201631/08/201640584345.24&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4345.24August201631/08/201640596500-32503250August201631/08/201640606500-32503250August201631/08/201640614506.68-2253.342253.34August201631/08/201640752513.34&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2513.34August201631/08/201640761473.34&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1473.34August201631/08/201640772166.67&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2166.67August201631/08/201640781516.67&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1516.67August201631/08/20164079417.18&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;417.18August201631/08/201610241408.33&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1408.33Sep201630/09/201610251408.33&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1408.33Sep201630/09/20161043780&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;780Sep201630/09/20161044866.67&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;866.67Sep201630/09/20161045823.34&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;823.34Sep201630/09/201610471238.4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1238.4Sep201630/09/201610541430&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1430Sep201630/09/201610901607.74&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1607.74Sep201630/09/201611361820&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1820Sep201630/09/201611531603.33&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1603.33Sep201630/09/201611541911.9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1911.9Sep201630/09/201619212253.33693.362946.69Sep201630/09/201623862094.3237.582331.88Sep201630/09/20162987182071.31891.3Sep201630/09/201634822231.662602491.66Sep201630/09/201636092307.9-1724.56583.34Sep201630/09/201640182036.66-2015.5321.13Sep201630/09/201640261868.45-182543.45Sep201630/09/201640962968.94&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2968.94Sep201630/09/2016&nbsp;ThanksJag

v-sihou-msft · Answer

jsb81
&nbsp;
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:&nbsp;
&nbsp;
1) No of new properties in the month
&nbsp;
No of new properties in the month =
SUMX (
    VALUES ( Table[Prop Code] ),
    IF (
        CALCULATE (
            COUNTROWS ( Table ),
            FILTER ( ALL ( Table ), Table[PaymentDate] &lt; MAX ( Table[PaymentDate] ) )
        )
            = 0
            &amp;&amp; CALCULATE ( COUNTROWS ( Table ) ) = 1,
        1,
        0
    )
)

2) No of properties suspended in the month
&nbsp;
No of properties suspended in the month =
SUMX (
    VALUES ( Table[Prop Code] ),
    IF (
        CALCULATE (
            COUNTROWS ( Table ),
            FILTER (
                ALL ( Table ),
                Table[Year] = MAX ( Table[Year] )
                    &amp;&amp; Table[MonthNumber]
                        = MAX ( Table[MonthNumber] ) - 1
            )
        )
            &gt; 0
            &amp;&amp; CALCULATE ( COUNTROWS ( Table ) ) = 0,
        1,
        0
    )
)
3) No of properties rent increased
&nbsp;
No of properties rent increased =
SUMX (
    VALUES ( Table[Prop Code] ),
    IF ( CALCULATE ( SUM ( Table[Adjustment] ) ) &gt; 0, 1, 0 )
)
4) No of properties rent decreased
&nbsp;
No of properties rent decreased =
SUMX (
    VALUES ( Table[Prop Code] ),
    IF ( CALCULATE ( SUM ( Table[Adjustment] ) ) &lt; 0, 1, 0 )
)
5) No of properties rent was same
&nbsp;
No of properties rent was same =
SUMX (
    VALUES ( Table[Prop Code] ),
    IF ( CALCULATE ( SUM ( Table[Adjustment] ) ) = BLANK (), 1, 0 )
)
Regards,

jsb81 · Answer

Hi Shiou,&nbsp;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.&nbsp;ThanksJag

Forum Discussion

Change in Data compared to previous month

2 Replies

Related content

Comparing values to previous months

Compare Against Previous Period KPI Card

Compare previous and new sales

Comparing perfomance previous years

Compare data with previous period/last month

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