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.
Hello everyone,
I really appreciate your help with this in advance, I have been trying to make it work for ages and failed miserably so far.
I have a consolidated data table that receives new entries every time a user modifies a SharePoint List. Within this table, multiple Properties, Buildings, Units and tenants are registered.
Keys:
Several KPIs are required, one of which is to have a measure or a new column that would allow me to register (Rent value) whenever a Tenant Value is changed from "Vacant" to "Tenant Name" or from a "Tenant Name" to a "New Tenant Name".
I have added indexes to group all different category with a single code but not so sure how to put them into a good use.
Please view my attached sample data file.
Thanks
H
@Calvin69 See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous
Hi @Greg_Deckler ,
I really apprecaite your feedback on this.
I have followed your instructions above but it seems that I am still doing somthing wrong:
Dax Code
PBICOM =
VAR Curren = '04ConsolidatedEntries'[Rent]
VAR PreviousDate = MAXX(
FILTER('04ConsolidatedEntries','04ConsolidatedEntries'[StartDate.Element:Text] <
EARLIER('04ConsolidatedEntries'[StartDate.Element:Text])), '04ConsolidatedEntries'[StartDate.Element:Text])
VAR Previous = MAXX(FILTER('04ConsolidatedEntries',[StartDate.Element:Text] = PreviousDate), [Rent])
RETURN
Curren - Previous
Table:
Expected values/behaviour are hilighted within the green column.
Thanks
H
@Calvin69 Can you post sample data?
Sure : https://drive.google.com/drive/folders/1-nhx9yKcLrrH3fcL-V3O7Kb4eSkdRKrH?usp=sharing
PropertyN Inclusive | BuildGroupIndex1 | BuildingN Inclusive | TitleGroupIndex1 | Title | TenantGroupIndex | TenantN Inclusive | StartDate.Element:Text | EndDate.Element:Text | VACorNot! | Index0 | Index1 | Rent | PBICOM | Expected |
Test Test Data Property | 27 | TES Test Data Building | 155 | Our Test Destination | 11 | Vacant | 01/01/2018 00:00 | 31/12/2019 | 1 | 729 | 730 | -75500 | ||
Test Test Data Property | 27 | TES Test Data Building | 155 | Our Test Destination | 65 | Power BI Limited | 01/01/2018 00:00 | 31/12/2019 | 0 | 730 | 731 | 2000 | -73500 | |
Test Test Data Property | 27 | TES Test Data Building | 155 | Our Test Destination | 65 | Power BI Limited | 01/01/2018 00:00 | 31/12/2019 | 0 | 731 | 732 | 4000 | -71500 | |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 65 | Power BI Limited | 01/01/2018 00:00 | 31/12/2019 | 0 | 732 | 733 | 4000 | -71500 | |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 65 | Power BI Limited | 01/01/2018 00:00 | 31/12/2019 | 0 | 733 | 734 | 4000 | -71500 | |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 65 | Power BI Limited | 01/01/2020 00:00 | 31/12/2021 | 0 | 734 | 735 | 8000 | -56568 | |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 65 | Power BI Limited | 01/01/2020 00:00 | 31/12/2021 | 0 | 735 | 736 | 10000 | -54568 | |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 65 | Power BI Limited | 01/01/2020 00:00 | 31/12/2021 | 0 | 736 | 737 | 10000 | -54568 | |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 65 | Power BI Limited | 01/01/2020 00:00 | 31/12/2021 | 0 | 737 | 738 | 10000 | -54568 | |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 65 | Power BI Limited | 01/01/2020 00:00 | 31/12/2021 | 0 | 752 | 753 | 10000 | -54568 | |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 65 | Power BI Limited | 01/01/2020 00:00 | 31/12/2021 | 0 | 753 | 754 | 10000 | -54568 | 10000 |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 11 | Vacant | 01/01/2020 00:00 | 31/12/2021 | 1 | 769 | 770 | 0 | -64568 | |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 11 | Vacant | 01/01/2020 00:00 | 31/12/2021 | 1 | 770 | 771 | 0 | -64568 | |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 11 | Vacant | 01/01/2020 00:00 | 31/12/2021 | 1 | 771 | 772 | 25000 | -39568 | |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 11 | Vacant | 01/01/2020 00:00 | 31/12/2021 | 1 | 772 | 773 | 35000 | -29568 | |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 11 | Vacant | 01/01/2020 00:00 | 31/12/2021 | 1 | 773 | 774 | 5000 | -59568 | |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 11 | Vacant | 01/01/2020 00:00 | 31/12/2021 | 1 | 774 | 775 | 5000 | -59568 | |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 11 | Vacant | 01/01/2020 00:00 | 31/12/2021 | 1 | 775 | 776 | 5000 | -59568 | |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 11 | Vacant | 01/01/2020 00:00 | 31/01/2022 | 1 | 776 | 777 | 5000 | -59568 | |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 11 | Vacant | 01/01/2020 00:00 | 06/02/2022 | 1 | 777 | 778 | 5000 | -59568 | |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 11 | Vacant | 01/01/2020 00:00 | 06/02/2022 | 1 | 778 | 779 | 8000 | -56568 | |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 66 | MCdo | 01/01/2020 00:00 | 06/02/2022 | 0 | 779 | 780 | 12000 | -52568 | |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 66 | MCdo | 01/01/2020 00:00 | 06/02/2022 | 0 | 1896 | 1897 | 12000 | -52568 | |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 66 | MCdo | 01/01/2020 00:00 | 06/02/2022 | 0 | 1897 | 1898 | 12000 | -52568 | |
Test Test Data Property | 27 | TES Test Data Building | 156 | Our Test Destination / V2 | 66 | MCdo | 01/01/2020 00:00 | 06/02/2022 | 0 | 1898 | 1899 | 20000 | -44568 | 20000 |
Test Test Data Property | 27 | TES Test Data Building | 180 | Mansion Central - Test | 77 | Fresh Water LTD | 01/06/2020 00:00 | 01/06/2021 | 0 | 1899 | 1900 | 50000 | 41700 | 50000 |
Test Test Data Property | 27 | TES Test Data Building | 180 | Mansion Central - Test | 78 | Bozo Fishing LTD | 01/06/2020 00:00 | 01/06/2021 | 0 | 1900 | 1901 | 100000 | 91700 | |
Test Test Data Property | 27 | TES Test Data Building | 180 | Mansion Central - Test | 78 | Bozo Fishing LTD | 01/06/2020 00:00 | 01/06/2021 | 0 | 1901 | 1902 | 100000 | 91700 | |
Test Test Data Property | 27 | TES Test Data Building | 180 | Mansion Central - Test | 78 | Bozo Fishing LTD | 01/06/2020 00:00 | 01/06/2021 | 0 | 1902 | 1903 | 120000 | 111700 | |
Test Test Data Property | 27 | TES Test Data Building | 180 | Mansion Central - Test | 78 | Bozo Fishing LTD | 01/06/2020 00:00 | 01/06/2022 | 0 | 1903 | 1904 | 120000 | 111700 | |
Test Test Data Property | 27 | TES Test Data Building | 180 | Mansion Central - Test | 78 | Bozo Fishing LTD | 01/06/2020 00:00 | 01/06/2022 | 0 | 1904 | 1905 | 120000 | 111700 | 120000 |
Total | 200000 |
Thanks
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 |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |