New column for latest month closing subscriber base

[Waseem]

Dear Community

I need urgent help with DAX formula. The situation is as under:

1. I have first table (Customers table) that contains six fields viz. Date, Product Code, Opening Base, Additions. churns and closing base
2. The data is pushed in on daily basis so for each day of month I have opening and closing base. So for example I have 2018 data for each day starting from Jan to Aug.
3. In Second table, I have list of product codes where I need to pull values from table one for "Closing Base" field of first table. However, I need to make sure that out of total avilable data, only last day's closing base is picked up (in this case for 31 August onl). And i need to keep this functionality dynamic for the next month (September) data load.
4. Similarly in table two i need first day's data to be picked up as opening balance (in this case opening base from customer table for Jan 1, 2018) 

I would appreciate help from all experts

Cheers

[v-yuta-msft]

Hi Waseem，

Could clarify more details about logic and share some dummy data for further test?

Regards,

Jimmy Tao

[Waseem]

@v-yuta-msft Hi

I have following table (in picture) for customers. This customer table has date wise and service name wise information of subscribers' opening base, closing base, gross additions and churns etc. So for each date service name codes are repeated for each field. Another Table is linked with this one by "Service Name" field. My objective is to pull certain fileds from this table into the second table as per following rules (the second table has only one column i.e. Service Name):

Opening Base (in second table should fetch data from this table)  = The opening base needs to be looked up for each service name as of first available date of the period only. In this case the date is January 1, 2018. So the DAX function should ignore all other data and for each service name should show information for first availabke date only.

Gross additions = It needs to pull service name wise sum of all entries of Gross Additions from first table.

Closing Base = This should bring service name wise records from table one for the last available date entries. In my data i have date wise and service name wise entries till August end. So the second table should automatically pull August 31st (for example) values as closing base.

Hope I clarified it.

Cheers 

[CheenuSing]

Hi @Waseem

Please clarify

a) The data will be available for only one year (eg 2018) for many years ( 2016,2017,2018 etc)

b) Will there be a filter by Year to show ouput for the relevant year as per your business logic.

c) Will there be a date range filter so that the opening base will be the start of the range and clsoing will be the end of the range.

If you can provide some sample data in excel format and share the link here it will help arrive at a solution.

Cheers

CheenuSing

[Waseem]

Hi @CheenuSing

As a second scenario, please help me if I keep only one year data in the source file, i.e. 2018 only.

Cheers

[CheenuSing]

Hi @Waseem

I have done 2 solutions for you - one based on Measures and other based on Power Query. The solution includes multi year scenario also.

Before we proceed to the steps in solutions, I added Year column in the table as Year = Year ([Month Year])

I Based on Measures

1.  measure_ServiceFirstDate = CALCULATE(FIRSTDATE('Sheet2'[Month Year]),

                                                                              ALLEXCEPT('Sheet2','Sheet2'[Service Name],  Sheet2[Year]))

   This calculates the ServiceFirstDate based on ServiceName and Year

2. measure_ServiceLastDate = CALCULATE(LASTDATE('Sheet2'[Month Year]),

                                                                               ALLEXCEPT('Sheet2','Sheet2'[Service Name], Sheet2[Year]))

    This calculates the ServiceFirstDate based on ServiceName and Year

3. Measure_opBase = Calculate(MIN(Sheet2[Opening Base]),FIlter(Sheet2,Sheet2[Month Year]=[measure_ServiceFirstDate]))

                 This calculates Opening Base

4. Measure_clBase = Calculate(MIN(Sheet2[Closing Base]),FIlter(Sheet2,Sheet2[Month Year]=[measure_ServiceLastDate]))

                This calculates Closing Base

5. Measure_Addition = sum(Sheet2[Gross Addition])

6. Measure_Churn = sum(Sheet2[Churns])

Plot a Table matrix.

II.  Based on Power Query

1. Using the GroupBY option - Advanced created the script

     #"Grouped Rows" = Table.Group(#"Inserted Year", {"Service Name","Year"}, {  

                                                                                 {"Total Addition", each List.Sum([Gross Addition]), type number},
                                                                                 {"Total Churn", each List.Sum([Churns]), type number},
                                                                                 {"AllRows", each _, type table},
                                                                                 {"opBase",each List.First([Opening Base]), type number},
                                                                                 {"clBase",each List.Last([Closing Base]), type number} ,
                                                                                  {"opDate",each List.First([Month Year]), type date},
                                                                                 {"clDate",each List.Last([Month Year]), type date}                                                                                    
                                                                                       }),

This step in the power query will create all the measures defined above but inside power query.

I have tested both the approaches for two years 2018 and 2019. 

I am attaching the link to the pbix file and data file for your reference.

If this solves your problem , please accept it as a solution and also give KUDOS.

pbix file  -  https://drive.google.com/open?id=1M4kvl5GKKJaEg88q1chrN_mv9aGXZ7El

data file - https://drive.google.com/open?id=1MyPnvfXox7De4XZo6BzC3QwEZZMMpJc7

Cheers

CheenuSing

[Waseem]

Many thanks @CheenuSing

Just one more thing before i could test the results, I need the results as a column in another table (and not measure). I have allocated many costs based on customer closing base, which should be looked up in another table against each service name. Measure would not work for this purpose i guess.

Cheers

[CheenuSing]

Hi @Waseem

The power query method populates the opening and closing base as columns in the base table.

Please share the second table and the value you want to populate to the second table from first table based on which relationship.

Cheers

CheenuSing

[Waseem]

Hi @CheenuSing

I am using following formula for now to pull closing base numbers into "Table 2" from "Subs" Table:

Closing Base = SUMX(FILTER('Subs','Subs'[Service Name]=[Service Name]&&'Subs'[Month Year]=LASTDATE('Subs'[Month Year])),'Subs'[Closing Base])

However I did not get closing base of last day. This formula is giving me closing base of entire 2018.

Appreciate your help

[CheenuSing · Accepted solution]

Hi @Waseem

From your reply it appears that you want to create a summarized table with just the service names, opening and closing balances.

What I have done is made a copy of the Sheet2 Table of my pbix that I uploaded and using edit query on the duplicated table I deleted the current code and pasted the following code

let
    Source = Excel.Workbook(File.Contents("C:\PowerBICommunity\Sampledata01.xlsx"), null, true),
    Sheet2_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Month Year", type date}, {"Service Name", type text}, {"Opening Base", Int64.Type}, {"Gross Addition", Int64.Type}, {"Churns", Int64.Type}, {"Closing Base", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Service Name", Order.Ascending}, {"Month Year", Order.Ascending}}),
    #"Inserted Year" = Table.AddColumn(#"Sorted Rows", "Year", each Date.Year([Month Year]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Year", {"Service Name","Year"}, {   {"Total Addition", each List.Sum([Gross Addition]), type number},
                                                                                 {"Total Churn", each List.Sum([Churns]), type number},
                                                                                 {"opBase",each List.First([Opening Base]), type number},
                                                                                 {"clBase",each List.Last([Closing Base]), type number} ,
                                                                                  {"opDate",each List.First([Month Year]), type date},
                                                                                 {"clDate",each List.Last([Month Year]), type date}                                                                                    
                                                                                       })
in
    #"Grouped Rows"

Using this I am able to generate ServiceName, Year, Opening Base, Closing Base, FirstDate, LastDate, Total Addition, TotalChurn.

Also doing it in power query the summarized table will be automatically updated with each refresh of the table.

Sample Output

Cheers

CheenuSing

[Waseem]

Many thanks @CheenuSing.. Highly appreciate your efforts. I have not copied the solution in my model yet but conceptualy it works on the data file you added. Thats exactly what i wanted. Stay blessed

Cheers

[Waseem]

Hi @CheenuSing

I have added the second sheet in the below link. I have this format in second table and need to lookup values from first table for opening and closing base according to first and last dat (you alraedy worked on). The output needs to be a column instead of a query.

https://www.dropbox.com/s/8o338phq6gdhyx4/Sample%20data.xlsx?dl=0

Many thanks 

[Waseem]

Hi @CheenuSing

Thanks for the kind response. The sample file link is as follows:

https://www.dropbox.com/s/8o338phq6gdhyx4/Sample%20data.xlsx?dl=0

a) The data for now is only for 2018 but going forward i shall add other years as well.

b) Yes I shall use a filter in the dashboards to show respective year data

c) I do not have a plan to insert any range option in the dashboard. So If I select 2017, the opening base should be Jan 01, 2017 number (per service), or whatever fist day entries are available in the data. Closing base is to be for the latest date number (per service) for selected year (in this case 2017).

Many thanks