I have a table which looks like this:Apt. Id einzug auszug Moveout MoveIn11101.01.201530.05.2018Moveout2018None11215.07.2017NULLNonemoveIn201711301.03.201730.04.2018Moveout2018moveIn2017 I created the last two columns moveOut and MoveIn based on the auszug and einzug respectively and also using several other conditions. Now I want to create a report which shows how many movein and moveout each month like this:

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	moveIn2017
113	01.03.2017	30.04.2018	Moveout2018	moveIn2017

I created the last two columns moveOut and MoveIn based on the auszug and einzug respectively and also using several other conditions.

Now I want to create a report which shows how many movein and moveout each month like this:

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.

9 Replies

jthomson

Solution Sage

8 years ago

Not sure what your other criteria are, but I would have thought you could just make a date table, relate your einzug and auszug columns to it (one'll need to be inactive, let's say the relationship to auszug), then just do a measure to count the rows in einzug and take away the rows in auszug, so something like:

Difference = countrows[yourtablename] - calculate(countrows[yourtablename],userelationship([auszug],datetable[date])

If you've got other criteria so that you're not just counting the number of instances in einzug/auszug for a different month, you can do some sort of countrows calculation on the movein/moveout columns and specify that it's not none, again you'd need to watch the relationships as I'd assume you're still trying to group by the dates in einzug/auszug

Anonymous
Not applicable
8 years ago
Hi jthomson, I tried like this using userrelationship but the problem is to calculate the MoveIn and MoveOut columns I already used einzug.[year] and auszug.[year] function and whenever i am connecting this table with the date table these two (einzug.[year] and auszug.[year] ) are not working and it's giving me error.

Anonymous

Not applicable

8 years ago

Hi jthomson,

I tried what you suggested but when I'm grouping them on Mon/Year it's giving me the only the result for the relationship which is active.

MoveOut = CALCULATE(
                    COUNTROWS(WohnungenErweitert), 
                            FILTER(WohnungenErweitert, WohnungenErweitert[name] <> "Leer" && WohnungenErweitert[name] <> RELATED('Apartment that are Leer'[Company])),
                            FILTER(WohnungenErweitert, WohnungenErweitert[wtyp] <> "H"),
                            USERELATIONSHIP(WohnungenErweitert[auszug],'Date New'[Date]))

MoveIn = CALCULATE(
                    COUNTROWS(WohnungenErweitert), 
                            FILTER(WohnungenErweitert, WohnungenErweitert[name] <> "Leer" && WohnungenErweitert[name] <> RELATED('Apartment that are Leer'[Company])),
                            FILTER(WohnungenErweitert, WohnungenErweitert[wtyp] <> "H"),
                            USERELATIONSHIP(WohnungenErweitert[einzug],'Date New'[Date]))

I am getting result like this: In this case I have auszug as active relationship.

YearMonthShort	MoveIn	MoveOut	Difference
2017/Jan	1	515	-514
2017/Feb	3	653	-650
2017/Mar	2	774	-772
2017/Apr		721	-721
2017/May	718	-718
2017/Jun		768	-768
2017/Jul	3	704	-701
2017/Aug	1	764	-763
2017/Sep		839	-839
2017/Oct	1	681	-680
2017/Nov		774	-774
2017/Dec	2	631	-629
2018/Jan		715	-715
2018/Feb		753	-753
2018/Mar		748	-748
2018/Apr		713	-713
2018/May	663	-663
2018/Jun	1	559	-558
2018/Jul		446	-446
2018/Aug		465	-465
2018/Sep		233	-233
2018/Oct		8	-8
2018/Nov		3	-3
2018/Dec		11	-11

sdjensen

Solution Sage

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] ) )
    )
)

Anonymous
Not applicable
8 years ago
Would a measure something like this work?:

MoveIn = calculate(count('Table'[Apartment Index]),or('Table'[MoveIn] ="MoveIn 2017",'Table'[MoveIn] ="MoveIn 2018"))
MoveOut = calculate(count('Table'[Apartment Index]),or('Table'[MoveOut] ="MoveOut 2017",'Table'[MoveOut] ="MoveOut 2018"))

Variance = MoveIn - MoveOut

If you then have a column grouped by month this should give you the grouped counts per month
- Anonymous
  Not applicable
  8 years ago
  Hi Anonymous,
  
  Problem here is I can't use month from one of the einzug or auszug because it changes the value of the other measure.
  - sdjensen
    Solution Sage
    8 years ago
    Perhaps you should have a look at this thread - I think you will be able to archieve what you want using the same technique: http://community.powerbi.com/t5/Desktop/How-to-Calculate-day-to-day-stock-based-on-a-filter-variable/m-p/410792#M188429
    
    Perhaps you need your In and Out to be in 2 different columns or to add column with "In" or "Out" in the value and then use this column when you create 2 sum measures on your In/Out column. The gain of using this approach will also be that you will be able to calculate your "stock" and see it develop over time.

Forum Discussion

Need help with a calculation

9 Replies

Related content

MoM calculation

Calculation Help

Calculate Tooltips

Hour Calculation

Calculating with Filters