DAX to calculate days elapsed from history table

[mwmckee]

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

[Phil_Seamark · Accepted 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])
            )

[mwmckee]

This is a great start Phil, not just a good one.

Thank you!

Michael