Reply
Frequent Visitor
Posts: 2
Registered: ‎12-27-2017

Change in Data compared to previous month

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 CodeAmountAdjustmentsPayment DueMonthYearPayment Date
10241408.33 1408.33July201631/07/2016
10251408.33 1408.33July201631/07/2016
1043780 780July201631/07/2016
1044866.67 866.67July201631/07/2016
1045823.34 823.34July201631/07/2016
10471238.4 1238.4July201631/07/2016
10541430 1430July201631/07/2016
10901607.74 1607.74July201631/07/2016
11361820 1820July201631/07/2016
11531603.33 1603.33July201631/07/2016
11541911.9 1911.9July201631/07/2016
18331825-1397428July201631/07/2016
20582312-57.672254.33July201631/07/2016
24152259.52 2259.52July201631/07/2016
31491781.55-1211660.55July201631/07/2016
34002389.88-13201069.88July201631/07/2016
3474217.26 217.26July201631/07/2016
34892253.3335.362288.69July201631/07/2016
37012368.15 2368.15July201631/07/2016
3899217.26 217.26July201631/07/2016
40723407.56-1993.341414.22July201631/07/2016
40733186.23-2340846.23July201631/07/2016
40742404.63-1646.67757.96July201631/07/2016
10241408.33                   1408.33August201631/08/2016
10251408.33                   1408.33August201631/08/2016
1043780                   780August201631/08/2016
1044866.67                   866.67August201631/08/2016
1045823.34                   823.34August201631/08/2016
10471238.4                   1238.4August201631/08/2016
10541430                   1430August201631/08/2016
10901607.74                   1607.74August201631/08/2016
11361820                   1820August201631/08/2016
11531603.33                   1603.33August201631/08/2016
11541911.9                   1911.9August201631/08/2016
14581968.6143.452012.06August201631/08/2016
14882123.3386.662209.99August201631/08/2016
20932042.2643.462085.72August201631/08/2016
2335130086.671386.67August201631/08/2016
39642036.66173.322209.98August201631/08/2016
396611916.75-7150.054766.7August201631/08/2016
40584345.24                   4345.24August201631/08/2016
40596500-32503250August201631/08/2016
40606500-32503250August201631/08/2016
40614506.68-2253.342253.34August201631/08/2016
40752513.34                   2513.34August201631/08/2016
40761473.34                   1473.34August201631/08/2016
40772166.67                   2166.67August201631/08/2016
40781516.67                   1516.67August201631/08/2016
4079417.18                   417.18August201631/08/2016
10241408.33               1408.33Sep201630/09/2016
10251408.33               1408.33Sep201630/09/2016
1043780               780Sep201630/09/2016
1044866.67               866.67Sep201630/09/2016
1045823.34               823.34Sep201630/09/2016
10471238.4               1238.4Sep201630/09/2016
10541430               1430Sep201630/09/2016
10901607.74               1607.74Sep201630/09/2016
11361820               1820Sep201630/09/2016
11531603.33               1603.33Sep201630/09/2016
11541911.9               1911.9Sep201630/09/2016
19212253.33693.362946.69Sep201630/09/2016
23862094.3237.582331.88Sep201630/09/2016
2987182071.31891.3Sep201630/09/2016
34822231.662602491.66Sep201630/09/2016
36092307.9-1724.56583.34Sep201630/09/2016
40182036.66-2015.5321.13Sep201630/09/2016
40261868.45-182543.45Sep201630/09/2016
40962968.94               2968.94Sep201630/09/2016

 



Thanks
Jag

Moderator
Posts: 2,588
Registered: ‎03-06-2016

Re: Change in Data compared to previous month

@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,

Frequent Visitor
Posts: 2
Registered: ‎12-27-2017

Re: Change in Data compared to previous month

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