Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 ID | Expiration | Expiration Days Left | Total Daily Rents | Total Remaining Rents |
1 | 5/31/2018 | 30 | $103 | $3,077 |
2 | 5/31/2018 | 30 | $146 | $4,367 |
3 | 8/18/2018 | 52 | $278 | $14,444 |
4 | 9/30/2018 | 95 | $185 | $17,606 |
5 | 12/20/2018 | 176 | $0 | $0 |
6 | 12/31/2018 | 187 | $80 | $14,997 |
7 | 12/31/2018 | 187 | $56 | $10,389 |
8 | 12/31/2018 | 187 | $433 | $81,033 |
9 | 3/14/2019 | 260 | $161 | $41,826 |
10 | 3/31/2019 | 277 | $83 | $22,900 |
11 | 3/31/2019 | 277 | $1,944 | $538,611 |
12 | 3/31/2019 | 277 | $4,444 | $1,231,111 |
13 | 3/31/2019 | 277 | $1,944 | $538,611 |
14 | 3/31/2019 | 277 | $1,460 | $404,497 |
15 | 4/30/2019 | 307 | $142 | $43,680 |
16 | 4/30/2019 | 307 | $512 | $157,253 |
17 | 4/30/2019 | 307 | $13 | $3,930 |
18 | 5/14/2019 | 321 | $196 | $62,898 |
19 | 5/31/2019 | 338 | $278 | $93,889 |
20 | 6/15/2019 | 353 | $164 | $57,804 |
21 | 6/30/2019 | 368 | $30 | $11,163 |
22 | 6/30/2019 | 368 | $150 | $55,147 |
23 | 6/30/2019 | 368 | $191 | $70,188 |
24 | 7/1/2019 | 369 | $419 | $154,570 |
25 | 7/15/2019 | 383 | $100 | $38,300 |
26 | 7/30/2019 | 398 | $52 | $20,598 |
27 | 8/31/2019 | 430 | $183 | $78,495 |
28 | 11/27/2019 | 518 | $36 | $18,706 |
29 | 12/31/2019 | 552 | $302 | $166,495 |
30 | 12/31/2019 | 552 | $222 | $122,667 |
31 | 12/31/2019 | 552 | $250 | $138,000 |
32 | 12/31/2019 | 552 | $139 | $76,667 |
33 | 12/31/2019 | 552 | $201 | $110,877 |
34 | 3/31/2020 | 643 | $1,144 | $735,342 |
35 | 4/2/2020 | 645 | $314 | $202,772 |
36 | 4/30/2020 | 673 | $635 | $427,676 |
37 | 6/30/2020 | 734 | $77 | $56,705 |
38 | 7/14/2020 | 748 | $118 | $88,306 |
39 | 7/31/2020 | 765 | $1,389 | $1,062,500 |
40 | 8/31/2020 | 796 | $278 | $221,111 |
41 | 9/30/2020 | 826 | $0 | $0 |
42 | 9/30/2020 | 826 | $1,374 | $1,135,063 |
43 | 9/30/2020 | 826 | $447 | $369,406 |
44 | 9/30/2020 | 826 | $490 | $404,408 |
45 | 10/31/2020 | 857 | $423 | $362,618 |
46 | 1/31/2021 | 949 | $1,231 | $1,167,797 |
47 | 2/28/2021 | 977 | $278 | $271,389 |
48 | 2/28/2021 | 977 | $1,389 | $1,356,944 |
49 | 3/31/2021 | 1008 | $243 | $245,274 |
50 | 6/30/2021 | 1099 | $73 | $80,642 |
51 | 7/31/2021 | 1130 | $833 | $941,667 |
52 | 3/1/2023 | 1708 | $1,619 | $2,765,914 |
53 | 7/17/2025 | 2577 | $1,023 | $2,637,130 |
54 | 12/31/2028 | 3840 | $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 ID | Expiration | Expiration Days Left | Total Daily Rents | Total Remaining Rents | Example Date | Example Amount left on Contract | Example Days left | |
1 | 5/31/2018 | 30 | $103 | $3,077 | 12/1/2019 | $0 | -549 | |
2 | 5/31/2018 | 30 | $146 | $4,367 | 12/1/2019 | $0 | -549 | |
3 | 8/18/2018 | 52 | $278 | $14,444 | 12/1/2019 | $0 | -470 | |
4 | 9/30/2018 | 95 | $185 | $17,606 | 12/1/2019 | $0 | -427 | |
5 | 12/20/2018 | 176 | $0 | $0 | 12/1/2019 | $0 | -346 | |
6 | 12/31/2018 | 187 | $80 | $14,997 | 12/1/2019 | $0 | -335 | |
7 | 12/31/2018 | 187 | $56 | $10,389 | 12/1/2019 | $0 | -335 | |
8 | 12/31/2018 | 187 | $433 | $81,033 | 12/1/2019 | $0 | -335 | |
9 | 3/14/2019 | 260 | $161 | $41,826 | 12/1/2019 | $0 | -262 | |
10 | 3/31/2019 | 277 | $83 | $22,900 | 12/1/2019 | $0 | -245 | |
11 | 3/31/2019 | 277 | $1,944 | $538,611 | 12/1/2019 | $0 | -245 | |
12 | 3/31/2019 | 277 | $4,444 | $1,231,111 | 12/1/2019 | $0 | -245 | |
13 | 3/31/2019 | 277 | $1,944 | $538,611 | 12/1/2019 | $0 | -245 | |
14 | 3/31/2019 | 277 | $1,460 | $404,497 | 12/1/2019 | $0 | -245 | |
15 | 4/30/2019 | 307 | $142 | $43,680 | 12/1/2019 | $0 | -215 | |
16 | 4/30/2019 | 307 | $512 | $157,253 | 12/1/2019 | $0 | -215 | |
17 | 4/30/2019 | 307 | $13 | $3,930 | 12/1/2019 | $0 | -215 | |
18 | 5/14/2019 | 321 | $196 | $62,898 | 12/1/2019 | $0 | -201 | |
19 | 5/31/2019 | 338 | $278 | $93,889 | 12/1/2019 | $0 | -184 | |
20 | 6/15/2019 | 353 | $164 | $57,804 | 12/1/2019 | $0 | -169 | |
21 | 6/30/2019 | 368 | $30 | $11,163 | 12/1/2019 | $0 | -154 | |
22 | 6/30/2019 | 368 | $150 | $55,147 | 12/1/2019 | $0 | -154 | |
23 | 6/30/2019 | 368 | $191 | $70,188 | 12/1/2019 | $0 | -154 | |
24 | 7/1/2019 | 369 | $419 | $154,570 | 12/1/2019 | $0 | -153 | |
25 | 7/15/2019 | 383 | $100 | $38,300 | 12/1/2019 | $0 | -139 | |
26 | 7/30/2019 | 398 | $52 | $20,598 | 12/1/2019 | $0 | -124 | |
27 | 8/31/2019 | 430 | $183 | $78,495 | 12/1/2019 | $0 | -92 | |
28 | 11/27/2019 | 518 | $36 | $18,706 | 12/1/2019 | $0 | -4 | |
29 | 12/31/2019 | 552 | $302 | $166,495 | 12/1/2019 | $9,060 | 30 | |
30 | 12/31/2019 | 552 | $222 | $122,667 | 12/1/2019 | $6,660 | 30 | |
31 | 12/31/2019 | 552 | $250 | $138,000 | 12/1/2019 | $7,500 | 30 | |
32 | 12/31/2019 | 552 | $139 | $76,667 | 12/1/2019 | $4,170 | 30 | |
33 | 12/31/2019 | 552 | $201 | $110,877 | 12/1/2019 | $6,030 | 30 | |
34 | 3/31/2020 | 643 | $1,144 | $735,342 | 12/1/2019 | $138,424 | 121 | |
35 | 4/2/2020 | 645 | $314 | $202,772 | 12/1/2019 | $38,622 | 123 | |
36 | 4/30/2020 | 673 | $635 | $427,676 | 12/1/2019 | $95,885 | 151 | |
37 | 6/30/2020 | 734 | $77 | $56,705 | 12/1/2019 | $16,324 | 212 | |
38 | 7/14/2020 | 748 | $118 | $88,306 | 12/1/2019 | $26,668 | 226 | |
39 | 7/31/2020 | 765 | $1,389 | $1,062,500 | 12/1/2019 | $337,527 | 243 | |
40 | 8/31/2020 | 796 | $278 | $221,111 | 12/1/2019 | $76,172 | 274 | |
41 | 9/30/2020 | 826 | $0 | $0 | 12/1/2019 | $0 | 304 | |
42 | 9/30/2020 | 826 | $1,374 | $1,135,063 | 12/1/2019 | $417,696 | 304 | |
43 | 9/30/2020 | 826 | $447 | $369,406 | 12/1/2019 | $135,888 | 304 | |
44 | 9/30/2020 | 826 | $490 | $404,408 | 12/1/2019 | $148,960 | 304 | |
45 | 10/31/2020 | 857 | $423 | $362,618 | 12/1/2019 | $141,705 | 335 | |
46 | 1/31/2021 | 949 | $1,231 | $1,167,797 | 12/1/2019 | $525,637 | 427 | |
47 | 2/28/2021 | 977 | $278 | $271,389 | 12/1/2019 | $126,490 | 455 | |
48 | 2/28/2021 | 977 | $1,389 | $1,356,944 | 12/1/2019 | $631,995 | 455 | |
49 | 3/31/2021 | 1008 | $243 | $245,274 | 12/1/2019 | $118,098 | 486 | |
50 | 6/30/2021 | 1099 | $73 | $80,642 | 12/1/2019 | $42,121 | 577 | |
51 | 7/31/2021 | 1130 | $833 | $941,667 | 12/1/2019 | $506,464 | 608 | |
52 | 3/1/2023 | 1708 | $1,619 | $2,765,914 | 12/1/2019 | $1,920,134 | 1186 | |
53 | 7/17/2025 | 2577 | $1,023 | $2,637,130 | 12/1/2019 | $2,102,265 | 2055 | |
54 | 12/31/2028 | 3840 | $4,331 | $16,630,966 | 12/1/2019 | $14,370,258 | 3318 |
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])
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
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:
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 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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |