Forum Discussion
Need help with a calculation
- 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 ChangedType2I 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.
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 |
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.
- Anonymous8 years agoNot applicable
Hi sdjensen
First of all Thank a lot for your detailed explanation. As you already mentioned yesterday I looked into your previous solution and was got stuck at one point.
Again here also, I do not have MoveIn and MoveOut columns. These are calculated columns which I created using the help of einzug and auszug (einzug.[year] and auszug.[year]) . Problem here is whenever I am creating a date table and connecting it with einzug and auszug, it started throwing me error in MoveIn and Moveout calculated columns (not considering .[year] function). If you could help me with this, that will be a great.
Thanks a ton again.
- sdjensen8 years ago
Solution Sage
Hi Anonymous,
I don't use either MoveIn or MoveOut from your table, so they don't have to be in your source table - I only use AppId, Einzug and Auszug.
I don't use Einzug or Auszug to connect to my dates table - I use the new Date column in my transformed table.