Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Greg_Deckler
Super User
Super User

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])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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])

Hi @Anonymous,

 

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.

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.