Calculating new column based on two or more tables

[SusuYes]

Happy new year everyone. 

I need to keep track of customers visting a system I have. The system is not able to record each login with a timestamp. What it does is accumulates the number of logins together. In the past, I have been manually exporting the data every week and updating it in Excel. This is the format of the report I get: 

CustomerID	Last Login (Date)	Number of Logins	other non related fields
254686	01 - Jan - 22022	8	Industry

I have been using this login to calculate the correct number of logins:

Actual Logins in Week N = Number of Logins in Week N - Number of Logins in Week N-1

The problems I'm facing with PowerBI are: 

• PowerBI only allows you to add a calculated coloumn between two tables only. I would have at least 56 tables by the end of the year. Is there a way I can navigate through that? I can create a Dimension Table that has all customer IDs and then relating one table at a time. 
• Is there a better way I can achieve while minimising manual input? 

any thoughts are appracited 🙂 

[Anonymous]

Hi @SusuYes ,

I have created a data sample based on the format:

Firstly ,add a week column:

Create a Calendar table to get all weeks of a year

Calendar = DISTINCT( SELECTCOLUMNS( CALENDAR(DATE(2022,1,1),DATE(2022,12,31)) ,"Week",WEEKNUM([Date])))

Then try:

numbers of login = 
var _total= LOOKUPVALUE('Table'[Number of Logins],'Table'[Week Number],[Week]) 
var _last=CALCULATE(MIN('Table'[Number of Logins]),FILTER('Table',[Week Number]=EARLIER('Calendar'[Week])-1))
return IF(_total<>BLANK(), _total - _last)

 Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SusuYes]

Hi @Anonymous ,

thanks a lot for your reply. 

This is getting closer to the probelm I'm facing. THe problem I am having is that if Customer ID 1 (as per your sample data) logs in again in Week 3, then his Number of logins will show as (Week 1 logins + Week 3 logins). I need to know the number of logins they have per week which is going to be Week 3 logins - Week 1 logins but I am not sure how to apply that for all cases. 

[SusuYes]

Thanks for your reply. 

I am not having a problem with the adding new data per say. The main problem is that the logins are accumulative. Therefore, if a customer logins in twice this week & three times last week, then last weeks report would show 3 logins and this weeks report would show 5. However, it won't show that this week, the customer logged only twice.

I'm trying to record how often a student is logging in so I'm planning to run Daily reports then subtract the latest Login number from the previous days Login number and I would get the daily logins. However, I couldn't acheive this on PowerBI. 

Hope this helps. I dont have data to share yet as we are at the begining of the year and it is a new system. 

[amitchandak]

@SusuYes , for this we can create a column or measure. Assume data is on week , student ID level

First, create a rank column in you table

Week Rank = RANKX(filter('Table', 'Table'[Student] =earlier(Table[Student]) ),'Table'[Year Week],,ASC,Dense)

then create a column

[Login Count] - maxx(filter(Table, Table[Week Rank]=earlier(Table[Week Rank]) -1), [Login Count])

For measure, you need a date/week table with yearweek column and then you need this week - last week for this week's login

column in date/week table

Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

[amitchandak]

@SusuYes , Are you getting tables each week ?

Then you should append them with timestamp of each week and use data table for analysis

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

Refer if needed

DAX append - for incremental for append

https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-...
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/

WOW

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8