Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Need help with a calculation

I have a table which looks like this: Apt. Id        einzug            auszug         Moveout           MoveIn 111 01.01.2015 30.05.2018 Moveout2018 None 112 15.07.2017 NULL None move...
  • sdjensen's avatar
    sdjensen
    8 years ago

    Anonymous - Like I said - you could use the approach from the thread that I linked to.

     

    I have created a test with the data you provided:

    So I start with a source table looking like this:

    And from this table I create the table needed to make the wanted calculations:

     

    Here is the Power Query code for this transformation:

    let
        Source = SourceTable,
        ChangedType = Table.TransformColumnTypes(Source,{{"AptId", type text}, {"einzug", type date}, {"auszug", type date}, {"Moveout", type text}, {"MoveIn", type text}}),
    
        InDate = Table.SelectColumns(ChangedType, {"AptId", "einzug"}),
        InDate_InOut = Table.AddColumn(InDate, "In/Out", each "In"),
        InDate_Count = Table.AddColumn(InDate_InOut, "Count", each 1),
        InDate_RenameColumn = Table.RenameColumns(InDate_Count, {{"einzug", "Date"}}),
    
        OutDate = Table.SelectColumns(Table.SelectRows(ChangedType, each [auszug] <> null), {"AptId", "auszug"}),
        OutDate_InOut = Table.AddColumn(OutDate, "In/Out", each "Out"),
        OutDate_Count = Table.AddColumn(OutDate_InOut, "Count", each -1),
        OutDate_RenameColumn = Table.RenameColumns(OutDate_Count, {{"auszug", "Date"}}),
    
        AppendInOut = Table.Combine({InDate_RenameColumn, OutDate_RenameColumn}),
        ChangedType2 = Table.TransformColumnTypes(AppendInOut,{{"Count", Int64.Type}})
    in
        ChangedType2

     

    I also added the date table like in the example that I posted - created the needed relationship between the tables and made 4 measures:

    MoveIn = 
    CALCULATE(
        SUM( Tabel1[Count] );
        Tabel1[In/Out] = "In"
    )
    
    MoveOut = 
    CALCULATE(
        -SUM( Tabel1[Count] );
        Tabel1[In/Out] = "Out"
    )
    
    Diff In/Out = [MoveIn] - [MoveOut]
    
    Appartments with tenants = 
    VAR MaxDate = CALCULATE( MAX( Tabel1[Date] ); ALL( 'Date' ) )
    RETURN
    IF(
        MIN( 'Date'[Date] ) <= MaxDate;
        CALCULATE(
            SUM( Tabel1[Count] );
            DATESBETWEEN( 'Date'[Date]; BLANK(); MAX( 'Date'[Date] ) )
        )
    )

     

    Here is my result - the top table show what you are asking for - the barchart show the measure "Appartments with tenants" by year (this could be taken a step further if you had the info about when an appartment was available from (and to) to calculate the number of total appartment available at a given date and then you could calculate the % of how many of the appartments that had tenants at any given time.