Problem with between startdate and terminaldate shown as one and after terminaldate as something els

[Anonymous]

Hi 

I hope someone can help me with my problem. I have a table called "fact" and in fact there is colomn with a startdate and a terminaldate. Between these dates it has to saw it a "gross add" after the terminaldate it has to say "churn". So i hope when i use filter on the date it show different counts. I will count on how many gross adds there will be and churns in etc special months that are filtered.

Hope someone can help

[DallasBaba]

@Anonymous You can add a custom column that determines whether a row is a "gross add" or a "churn" based on the date range.

You can do this by using the Table.AddColumn function and a conditional statement.
Assuming your "fact" table has columns named "StartDate" and "TerminalDate," add the following custom column:

= Table.AddColumn(#"PreviousStep", "Status", each
if [StartDate] <= Date.From(DateTime.LocalNow()) and [TerminalDate] >= Date.From(DateTime.LocalNow()) then "Gross Add"
else if [StartDate] > Date.From(DateTime.LocalNow()) then "Future"
else "Churn"
)

Next, you can filter and count these statuses for specific months.
Assuming you want to filter for a specific month, you can use the Table.SelectRows function to filter the data and then use Table.RowCount to count the "Gross Adds" and "Churns" within that filtered data.

For example:

let
Source = YourSourceData, // Replace with the step that loads your data
SelectedMonth = #datetime(2023, 10, 1, 0, 0, 0), // Replace with your desired date

// Filter the data for the selected month
FilteredData = Table.SelectRows(Source, each [StartDate] <= SelectedMonth and [TerminalDate] >= SelectedMonth),

// Count Gross Adds and Churns
GrossAddsCount = Table.RowCount(Table.SelectRows(FilteredData, each [Status] = "Gross Add")),
ChurnsCount = Table.RowCount(Table.SelectRows(FilteredData, each [Status] = "Churn"))
in
[GrossAddsCount, ChurnsCount]

In this example, you will be filtering the data for the specified month (October 2023) and then counting the "Gross Adds" and "Churns" within that filtered data.

Note: Remember to replace "YourSourceData" with the actual step that loads your "fact" table.

You can adjust the SelectedMonth variable to filter the data for the desired month.