How to calculate the amount of clients by status by each month to know how many clients were Active or OnBoarding every month

user_9630858 0
Viewed 86

I am moving reports from Cooladata (BigQuery) to Power BI. I have a table of clients in different statuses (Live,OnBoarding, Disabled). For each status I have a date (if it has happened). What I want to get is to see how many clients were in this status for each month. For instance, if I have 2 clients opened in March, 2 in April and 2 in May, I need to see 6 clients in May. But it is not cumulative cause can happen that someone of these clients was moved to the status disabled in May. Actually I am simply checking how many clients were in each status Disabled for the particular Month.

! See the table with data. Open in Explorer

In BigQuery I have created a table that returns last date of month every month and checks whether the date when the client started Boarding or was Changed to the status live or Disabled happened before these date. The same client is checked in March,April,May etc. I check this condition on a monthly basis and thus get the required sum of accounts by type.

    SELECT count( case when ChangedToLiveOn<=last_day_of_month and (DisabledOn>last_day_of_month OR DisabledOn is null) then 1 end) as Clients_Live, 
           count(case when StartedOnBoardingDate<=last_day_of_month and (ChangedToLiveOn>last_day_of_month OR ChangedToLiveOn is null) and (DisabledOn>last_day_of_month OR DisabledOn is null) then 1 end) OnBoarding.

The question how I can build the same in Power Bi. I do not understand how I can apply the same logic.

Also in SQL Server I get the required data by using the loopenter code here:

    DECLARE @date DATETIME ,
@DAtediffM INT;

SET @date = GETDATE()
SET @DatediffM = 0

WHILE @DatediffM >= -12
BEGIN

DECLARE @last_day_of_month DATETIME
SET @last_day_of_month= EOMONTH ( @date, @DatediffM  )
SET @DatediffM = @DatediffM - 1

SELECT @last_day_of_month, 
COUNT(CASE WHEN ntw_ChangedToLiveOn<=@last_day_of_month and (ntw_DisabledOn>@last_day_of_month OR ntw_DisabledOn is null)  then 1 END) 
+  COUNT(CASE WHEN ntw_StartedOnBoardingDate<=@date and (ntw_ChangedToLiveOn>@last_day_of_month OR ntw_ChangedToLiveOn is null) and  (ntw_DisabledOn>@last_day_of_month OR ntw_DisabledOn is null)  then 1 END) AS Total ,
   COUNT(CASE WHEN ntw_ChangedToLiveOn<=@last_day_of_month and (ntw_DisabledOn>@last_day_of_month OR ntw_DisabledOn is null)   then 1 end) AS Clients_Live,
   COUNT(CASE WHEN ntw_StartedOnBoardingDate<=@date and (ntw_ChangedToLiveOn>@last_day_of_month OR ntw_ChangedToLiveOn is null) and 
(ntw_DisabledOn>@last_day_of_month OR ntw_DisabledOn is null)  then 1 end) OnBoarding,

FROM AccountBase 

WHERE  ((DATEDIFF(month,ntw_ChangedToLiveOn, GETDATE()) <=12  OR ntw_ChangedToLiveOn is null) or(DATEDIFF(month,ntw_DisabledOn, GETDATE()) <=12  OR ntw_DisabledOn is null))  



Print @last_day_of_month
END

But I can't move it to Power Bi.

powerbi
0 Answers
Related