Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,😊
Thank you in advance for attempting to read through this and for any help that you could provide me with.
I am trying to build a measure or a column that would allow me to do the following:
Table:
Date | Tenant | Start | Review | End | Rent |
01/01/2019 | Vacant | 01/10/2019 | 01/10/2021 | 0 | |
01/01/2020 | Voda | 01/01/2020 | 01/10/2020 | 10000 | |
01/01/2020 | Hitash | 15/11/2020 | 01/06/2021 | 01/10/2021 | 15000 |
01/01/2020 | Sports ltd | 01/01/2020 | 15/06/2021 | 31/12/2021 | 5000 |
01/07/2021 | Vacant | 01/10/2019 | 01/10/2021 | 0 | |
01/07/2021 | Voda | 15/11/2020 | 01/10/2021 | 10000 | |
01/07/2021 | Hitash | 15/11/2020 | 01/10/2021 | 17000 | |
01/07/2021 | Sports ltd | 01/01/2020 | 01/11/2021 | 31/12/2021 | 7000 |
Target: Total of "4000" should appear based on the following logic
On the 01/07/2021 | |
If new review date is Blank or Greater than previous review date and the Tenant Name is the same then the following should take place | |
Hitash | New Rent Value (17000) - Old Rent Value (15000) = 2000 |
Sports ltd | New Rent Value (7000) - Old Rent Value (5000) = 2000 |
Total Rent difference = 2000 + 2000 = 4000 |
Thanks
H
Solved! Go to Solution.
@Calvin69 Made a few assumptions along the way but maybe this:
Measure =
VAR __Table = 'Table4'
VAR __MaxDate = MAX('Table4'[Date])
VAR __PreviousDate = MAXX(FILTER('Table4',[Date]<__MaxDate),[Date])
VAR __MaxDateTable = FILTER(__Table,[Date]=__MaxDate)
VAR __PreviousDateTable = FILTER(__Table,[Date]=__PreviousDate)
VAR __FinalTable =
ADDCOLUMNS(
ADDCOLUMNS(
__MaxDateTable,
"__Include",
VAR __Review = [Review]
RETURN
IF(ISBLANK([Review]) || COUNTROWS(FILTER(__PreviousDateTable,[Review]<__Review))>0,1,0)
),
"__Diff",
VAR __Tenant = [Tenant]
RETURN
IF([__Include]=1,[Rent] - MAXX(FILTER(__PreviousDateTable,[Tenant]=__Tenant),[Rent]),0)
)
RETURN
SUMX(FILTER(__FinalTable,[__Include]=1),[__Diff])
@Calvin69 Made a few assumptions along the way but maybe this:
Measure =
VAR __Table = 'Table4'
VAR __MaxDate = MAX('Table4'[Date])
VAR __PreviousDate = MAXX(FILTER('Table4',[Date]<__MaxDate),[Date])
VAR __MaxDateTable = FILTER(__Table,[Date]=__MaxDate)
VAR __PreviousDateTable = FILTER(__Table,[Date]=__PreviousDate)
VAR __FinalTable =
ADDCOLUMNS(
ADDCOLUMNS(
__MaxDateTable,
"__Include",
VAR __Review = [Review]
RETURN
IF(ISBLANK([Review]) || COUNTROWS(FILTER(__PreviousDateTable,[Review]<__Review))>0,1,0)
),
"__Diff",
VAR __Tenant = [Tenant]
RETURN
IF([__Include]=1,[Rent] - MAXX(FILTER(__PreviousDateTable,[Tenant]=__Tenant),[Rent]),0)
)
RETURN
SUMX(FILTER(__FinalTable,[__Include]=1),[__Diff])
Hi @Greg_Deckler ,
Spot on mate, wonderful and thank you so much.
Just wondering why do you add __ before each variable name?
H
@Calvin69 Best explained as old habit I suppose. I found it advantageous to flag things I created versus what is actually in the data model. Comes in particularly handly when using ADDCOLUMNS and more complex measures as it can become a bit confusing to figure out what is being referred to in what context. I use a double underscore because it fits the rhythm of my typing better.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |