cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
seroskij Frequent Visitor
Frequent Visitor

Add Up Total Remaining on Contract After Date in Context

I am trying to figure out how much rents I have left on my properties. I'd like to create a line chart with my Expiration dates on the X-axis and Rent remaining on the y-Axis. 

 

I have a Dimensional Date table and a Fact table for my leases. My data looks like the table below. 

 

Property IDExpirationExpiration Days LeftTotal Daily RentsTotal Remaining Rents
15/31/201830$103$3,077
25/31/201830$146$4,367
38/18/201852$278$14,444
49/30/201895$185$17,606
512/20/2018176$0$0
612/31/2018187$80$14,997
712/31/2018187$56$10,389
812/31/2018187$433$81,033
93/14/2019260$161$41,826
103/31/2019277$83$22,900
113/31/2019277$1,944$538,611
123/31/2019277$4,444$1,231,111
133/31/2019277$1,944$538,611
143/31/2019277$1,460$404,497
154/30/2019307$142$43,680
164/30/2019307$512$157,253
174/30/2019307$13$3,930
185/14/2019321$196$62,898
195/31/2019338$278$93,889
206/15/2019353$164$57,804
216/30/2019368$30$11,163
226/30/2019368$150$55,147
236/30/2019368$191$70,188
247/1/2019369$419$154,570
257/15/2019383$100$38,300
267/30/2019398$52$20,598
278/31/2019430$183$78,495
2811/27/2019518$36$18,706
2912/31/2019552$302$166,495
3012/31/2019552$222$122,667
3112/31/2019552$250$138,000
3212/31/2019552$139$76,667
3312/31/2019552$201$110,877
343/31/2020643$1,144$735,342
354/2/2020645$314$202,772
364/30/2020673$635$427,676
376/30/2020734$77$56,705
387/14/2020748$118$88,306
397/31/2020765$1,389$1,062,500
408/31/2020796$278$221,111
419/30/2020826$0$0
429/30/2020826$1,374$1,135,063
439/30/2020826$447$369,406
449/30/2020826$490$404,408
4510/31/2020857$423$362,618
461/31/2021949$1,231$1,167,797
472/28/2021977$278$271,389
482/28/2021977$1,389$1,356,944
493/31/20211008$243$245,274
506/30/20211099$73$80,642
517/31/20211130$833$941,667
523/1/20231708$1,619$2,765,914
537/17/20252577$1,023$2,637,130
5412/31/20283840$4,331$16,630,966

 

In essence, I need help creating a dax formula that calculates the total rent remaining on my properties by calculating the number of days left on the contracts by the dates on the x-axis, taking the product of the days left and total daily rents, and returning that value. For example: the formula would return the value $ 21,950,753 based on a date of 12/1/2019

 

Property IDExpiration Expiration Days LeftTotal Daily RentsTotal Remaining RentsExample DateExample Amount left on ContractExample Days left
15/31/2018 30$103$3,07712/1/2019$0-549
25/31/2018 30$146$4,36712/1/2019$0-549
38/18/2018 52$278$14,44412/1/2019$0-470
49/30/2018 95$185$17,60612/1/2019$0-427
512/20/2018 176$0$012/1/2019$0-346
612/31/2018 187$80$14,99712/1/2019$0-335
712/31/2018 187$56$10,38912/1/2019$0-335
812/31/2018 187$433$81,03312/1/2019$0-335
93/14/2019 260$161$41,82612/1/2019$0-262
103/31/2019 277$83$22,90012/1/2019$0-245
113/31/2019 277$1,944$538,61112/1/2019$0-245
123/31/2019 277$4,444$1,231,11112/1/2019$0-245
133/31/2019 277$1,944$538,61112/1/2019$0-245
143/31/2019 277$1,460$404,49712/1/2019$0-245
154/30/2019 307$142$43,68012/1/2019$0-215
164/30/2019 307$512$157,25312/1/2019$0-215
174/30/2019 307$13$3,93012/1/2019$0-215
185/14/2019 321$196$62,89812/1/2019$0-201
195/31/2019 338$278$93,88912/1/2019$0-184
206/15/2019 353$164$57,80412/1/2019$0-169
216/30/2019 368$30$11,16312/1/2019$0-154
226/30/2019 368$150$55,14712/1/2019$0-154
236/30/2019 368$191$70,18812/1/2019$0-154
247/1/2019 369$419$154,57012/1/2019$0-153
257/15/2019 383$100$38,30012/1/2019$0-139
267/30/2019 398$52$20,59812/1/2019$0-124
278/31/2019 430$183$78,49512/1/2019$0-92
2811/27/2019 518$36$18,70612/1/2019$0-4
2912/31/2019 552$302$166,49512/1/2019$9,06030
3012/31/2019 552$222$122,66712/1/2019$6,66030
3112/31/2019 552$250$138,00012/1/2019$7,50030
3212/31/2019 552$139$76,66712/1/2019$4,17030
3312/31/2019 552$201$110,87712/1/2019$6,03030
343/31/2020 643$1,144$735,34212/1/2019$138,424121
354/2/2020 645$314$202,77212/1/2019$38,622123
364/30/2020 673$635$427,67612/1/2019$95,885151
376/30/2020 734$77$56,70512/1/2019$16,324212
387/14/2020 748$118$88,30612/1/2019$26,668226
397/31/2020 765$1,389$1,062,50012/1/2019$337,527243
408/31/2020 796$278$221,11112/1/2019$76,172274
419/30/2020 826$0$012/1/2019$0304
429/30/2020 826$1,374$1,135,06312/1/2019$417,696304
439/30/2020 826$447$369,40612/1/2019$135,888304
449/30/2020 826$490$404,40812/1/2019$148,960304
4510/31/2020 857$423$362,61812/1/2019$141,705335
461/31/2021 949$1,231$1,167,79712/1/2019$525,637427
472/28/2021 977$278$271,38912/1/2019$126,490455
482/28/2021 977$1,389$1,356,94412/1/2019$631,995455
493/31/2021 1008$243$245,27412/1/2019$118,098486
506/30/2021 1099$73$80,64212/1/2019$42,121577
517/31/2021 1130$833$941,66712/1/2019$506,464608
523/1/2023 1708$1,619$2,765,91412/1/2019$1,920,1341186
537/17/2025 2577$1,023$2,637,13012/1/2019$2,102,2652055
5412/31/2028 3840$4,331$16,630,96612/1/2019$14,370,2583318
5 REPLIES 5
Highlighted
Super User
Super User

Re: Add Up Total Remaining on Contract After Date in Context

Try this measure:

 

Measure = 
VAR __Date = DATE(2019,12,1) //Replace this with something like a MAX([Column]) that you would choose from a slicer
VAR __tmpTable = 
ADDCOLUMNS(
    FILTER(
        SELECTCOLUMNS('Properties',"__Expiration",[Expiration],"__Total Daily Rents",[Total Daily Rents]),
        [__Expiration]>__Date
    ),
    "__RentLeft",DATEDIFF(__Date,[__Expiration],DAY)*[__Total Daily Rents]
)
RETURN SUMX(__tmpTable,[__RentLeft])

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

seroskij Frequent Visitor
Frequent Visitor

Re: Add Up Total Remaining on Contract After Date in Context

Thanks for the reply.

 

It's actually coming up blank.

 

 

Measure =
VAR __Date = MAX(Dim_Date[Date]) 
VAR __tmpTable =
  ADDCOLUMNS(
    FILTER( SELECTCOLUMNS(Fact_Leases,"__Expiration",[Expiration],"__Total Daily Rents",Fact_Leases[Daily Rents]), [__Expiration]>__Date ), "__RentLeft",DATEDIFF(__Date,[__Expiration],DAY)*[__Total Daily Rents])
RETURN SUMX(__tmpTable,[__RentLeft])

Super User
Super User

Re: Add Up Total Remaining on Contract After Date in Context

Hmm, here is the M code that I created my "Properties" table with:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZZJbiMxDEWvYhhZEjAnidJZgr7/NVriUCWgy4veOJX4iUV+flL5/X3TG97tI/RhpLGeBdfHD6G89k8BNHu9/8Dvm59B7Q4qSE9Q1u/jQ6PAtk/+sI1XHABVDVLXH+ZHsMjZnBgtSIOOPcj9BfHiCiXrm8FXfm6oB3SnSMP21wPrzXNmjvYFbVENIciYgY4vqEpINAhwP252bl0+pBvdz9xDpE4hEsHgrIjQ2QjrrEWyEZUZJmZdRM8owdxK7rRlQCdKnJ/xFN4PshDQdUD+L75+w7WH0orrXSU17d5pdtkFwsCVAxbooyrtz2yjYKkZcEuxyb4ELutOqbBuw6MvwuTkjHZ3hjFHsvPwubMyTv9OgVHW4N3D/qF2sU2i3ymbwcD0ur+xn9l2jytpztWPnoUxP6PUgm0NSGsq5Qs7w3KGQCNL4904+9yV9RmunCmuQrOUjFuwd2kjSsPIYRlCyp/cnT1ymJ5Di6YxQit12Xw53OpqrpG0vY1lnJasD9sireBGIVkO6QCr/cDznFJnY+0IpnN6vyP7O59o5qTX+PVrn9E3OvtBSwwsMYS/0CQhs/UjtHwLjZS2QBi1geUYvLCeRk+WHcJwJg1kz5XjMXh8075ehTTbwmBW7DF4AZuH7hK7WHktY0uxxW7POWye2c/O03fo6kspPcJGeqEa9qSYprFtVGFn2Ogqz3rcB7GL/QnXqLaSWvE2UxyY/RxV5mPJ6e7iPJIe/O8NovwMrRSsVictjbFG1RvwcEA1tFgjBloFqn6B57E2MSfFm0V4VDdarHHO/bak6FS0330Je6U637XpK/FuYLWVdcda1hjXgVjil3ZG9xXoPXuAz87Iavu8LvZ5ONXHBzG2qOb1pg3Ykm54+CnoOcPOYRKEXqZudJjEWYoNMvI6nuuSvearxVXosI+aRRoLyaW3ZqA3mFSpSPh1rxxfgdyqVEzdl+piywVpmabHCPvKkuHG3P8SlfZr5AVh9m2EP38B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Property ID" = _t, Expiration = _t, #"Expiration Days Left" = _t, #"Total Daily Rents" = _t, #"Total Remaining Rents" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Property ID", Int64.Type}, {"Expiration", type date}, {"Expiration Days Left", Int64.Type}, {"Total Daily Rents", Currency.Type}, {"Total Remaining Rents", Currency.Type}})
in
    #"Changed Type"

And here it is working:

 

image.png

 

One way to troubleshoot would be to create a new table and paste this in:

 

Table = 
VAR __Date = DATE(2019,12,1) RETURN ADDCOLUMNS( FILTER( SELECTCOLUMNS(Fact_Leases,"__Expiration",[Expiration],"__Total Daily Rents",Fact_Leases[Daily Rents]), [__Expiration]>__Date ), "__RentLeft",DATEDIFF(__Date,[__Expiration],DAY)*[__Total Daily Rents])

Just to see if you are getting what you expect back.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Community Support Team
Community Support Team

Re: Add Up Total Remaining on Contract After Date in Context

Hi @seroskij,

 

Have you solved your problem?

 

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If you still need help, please feel free to ask.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
seroskij Frequent Visitor
Frequent Visitor

Re: Add Up Total Remaining on Contract After Date in Context

I think this is close, but not quite right. Each year remaining should have a value in it until 2028. In essence, I'm thinking it should be like a reverse running total. For example, if I look at 2019, it would show the maximum amount of rents remaining.

 

 

 

 

Capture.PNG

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 4,012 guests
Please welcome our newest community members: