Forum Discussion
sjehanzeb
Resolver I
5 years agoCombining calendar table with calculated amount from another table in addition to customer id
This is in continuation of a Previous post. My objective is to create a table with month wise running balance of individual customer. My transactional data is simply a list of transactions (plus/minus).
I created a calendar table with individual months so that balance still shows even if there is no transactions. my issue is that I am unable to add customer id to the summarize table.
Calender = my own calender table that is linkedin to transactional table's date column
SF = Transactional Table
SF[Line Amt] = Transactions (plus / minus)
SF[ID] = Individual customer ID (cannot insert it here)
SUMMARIZE(Calender, Calender[Year Month], Calender[Year Month Number], "Amount", CALCULATE(sum(SF[Line Amt]), FILTER(ALL(SF), RELATED(Calender[Year Month Number]) <= EARLIER(Calender[Year Month Number]))))
- So, I resolved it. Combine two tables with all values
var calendertable = SUMMARIZE(Calender, Calender[Year Month])var SFtable= SUMMARIZE(SF, SF[ID])return
CROSSJOIN(calendertable, SFtable)
1 Reply
- sjehanzeb
Resolver I
So, I resolved it. Combine two tables with all values
var calendertable = SUMMARIZE(Calender, Calender[Year Month])var SFtable= SUMMARIZE(SF, SF[ID])return
CROSSJOIN(calendertable, SFtable)