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.
Greetings!
I am presented with a table containing history of residential units with each record representing when a tenant moves in and subsequently moves out. A sample extract with one unit only is below.
I would like to generate a report that shows the vacant days for each unit as follows.
Can this be addressed using DAX?
I have also been considering using M to create a summary table (somehow) with a row for each day of the year and each unit with a simple column of Vacant (0/1) which would simplify summations. Is this a better approach?
Thank you in advance for any insight or ideas.
Michael
Solved! Go to Solution.
Hi @mwmckee
Here is a way you can create a new calculated table in DAX that has a good crack at working out the gaps inbetween.
Not sure how you plan to allocate dates empty when that period spans two years, but this is hopefully a good start.
New Table = VAR StartingPoint = ADDCOLUMNS( 'Table1', "Guaranteed Ranking", CALCULATE( COUNTROWS('Table1'), FILTER(ALL('Table1'), 'Table1'[MoveIn] < EARLIER('Table1'[MoveIn]) && 'Table1'[UnitID] = EARLIER('Table1'[UnitID]) ) )+1) VAR JoinTable = SELECTCOLUMNS( StartingPoint, "UnitID-j",[UnitID], "MoveIn-j",[MoveIn], "Rank-j",[Guaranteed Ranking]-1 ) VAR JoinedTable = FILTER( CROSSJOIN(StartingPoint,JoinTable), [UnitID] = [UnitID-j] && [Guaranteed Ranking] = [Rank-j] ) RETURN SELECTCOLUMNS( JoinedTable , "UnitID" , [UnitID] , "MoveOut" , [MoveOut] , "MoveInt" , [MoveIn-j] , "Dates Empty" , DATEDIFF([MoveOut] ,[MoveIn-j],DAY) , "Year" , YEAR('Table1'[MoveIn]) )
Hi @mwmckee
Here is a way you can create a new calculated table in DAX that has a good crack at working out the gaps inbetween.
Not sure how you plan to allocate dates empty when that period spans two years, but this is hopefully a good start.
New Table = VAR StartingPoint = ADDCOLUMNS( 'Table1', "Guaranteed Ranking", CALCULATE( COUNTROWS('Table1'), FILTER(ALL('Table1'), 'Table1'[MoveIn] < EARLIER('Table1'[MoveIn]) && 'Table1'[UnitID] = EARLIER('Table1'[UnitID]) ) )+1) VAR JoinTable = SELECTCOLUMNS( StartingPoint, "UnitID-j",[UnitID], "MoveIn-j",[MoveIn], "Rank-j",[Guaranteed Ranking]-1 ) VAR JoinedTable = FILTER( CROSSJOIN(StartingPoint,JoinTable), [UnitID] = [UnitID-j] && [Guaranteed Ranking] = [Rank-j] ) RETURN SELECTCOLUMNS( JoinedTable , "UnitID" , [UnitID] , "MoveOut" , [MoveOut] , "MoveInt" , [MoveIn-j] , "Dates Empty" , DATEDIFF([MoveOut] ,[MoveIn-j],DAY) , "Year" , YEAR('Table1'[MoveIn]) )
This is a great start Phil, not just a good one.
Thank you!
Michael
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |